How to import certain sheets out of workbooks into an other workbook from a folder? (VBA)

Question

Dear Stackoverflow members,

In the past i created with the help of stack overflow a dynamic import script. This script imports all the sheet's from workbooks in a folder.

But for now i want to only import sheets called 'sheet1', instead of all sheets from a workbook (sheet 1, 2 and 3).

I tried to slightly modify the 'worksheets' variable, but i couldn't get it to work.

The code consist of a macro script and a function: This is the Sub

Public Sub Import_sheets_from_dir()
' Import script from http://www.excel-easy.com/vba/examples/import-sheets.html
' Function script from http://stackoverflow.com/questions/19372319/vba-folder-picker-set-where-to-start
On Error GoTo ErrMsg
Dim directory As String
Dim fileName As String
Dim Sheet As Worksheet
Set ActivoWB = ActiveWorkbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = GetFolderName() & "/"
fileName = Dir(directory & "*.xl??")

Do While fileName <> ""
    Workbooks.Open (directory & fileName)

    For Each Sheet In Workbooks(fileName).Worksheets
        Workbooks(fileName).Worksheets(Sheet.Name).Copy _
        After:=ActivoWB.Sheets(ActivoWB.Sheets.Count)
    Next Sheet

    Workbooks(fileName).Close
    fileName = Dir()
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Exit Sub

ErrMsg:
MsgBox prompt:="your doing something wrong"


End Sub

This is the used function:

Public Function GetFolderName(Optional OpenAt As String) As String
Dim lCount As Long

GetFolderName = vbNullString

With Application.FileDialog(msoFileDialogFolderPicker)
    .InitialFileName = OpenAt
    .Show
    For lCount = 1 To .SelectedItems.Count
        GetFolderName = .SelectedItems(lCount)
    Next lCount
End With
End Function

I also wonder which name is more relevant to change, i would like to use the VBA name like (the red, instead of the blue (is sheet name displayed in Excel)). Print Screen from VBA window I would like to learn what would be the difference in coding for both.

I tried to modify the loop For Each Sheet In Workbooks(fileName).Sheet1, but this didn't work.

Can you please guide me in the right direction?

Thanks in advance!

Regards, Dubblej

Paul Ogilvie Paul Ogilvie · Accepted Answer · 2016-11-04T11:33:39

Couldn't it be as simple as:

For Each Sheet In Workbooks(fileName).Worksheets
    If (Sheet.Name = "Sheet1") Then
        Sheet.Copy _
        After:=ActivoWB.Sheets(ActivoWB.Sheets.Count)
    Endif
Next Sheet

How to import certain sheets out of workbooks into an other workbook from a folder? (VBA)

1 Answers