VBA Import multiple sheets into Workbook

Question

I have the following code which allows me to select a single file and import the "Pens" tab from it, however I would like to select multiple files.

I would like to be able to select the "Pens" tab from multiple workbooks each on its own tab in the consolidated workbook.

Could you please assist in how this might work? I think this might require the use of the For Each function but not sure how to structure this.

Thanks very much in advance

Sub ImportActiveList()
    Dim FileName As String
    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Dim ActiveListWB As Workbook

    Set WS2 = ActiveWorkbook.Sheets("AllPens")
    FileName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*),*.xls*", _
                                           Title:="Select Active List to Import", _
                                           MultiSelect:=False)

    If FileName = "False" Then
        Exit Sub
    Else
        Set ActiveListWB = Workbooks.Open(FileName)
    End If

    Set WS1 = ActiveListWB.Sheets("Pens")

    WS1.UsedRange.Copy WS2.Range("A1")

    ActiveWorkbook.Close False

End Sub

Leviathan · Accepted Answer · 2016-05-05T11:52:00.407

You need a For Each loop if you have a collection or an array of objects or values that you want to iterate. See the documentation for the syntax and examples of its usage.

If you change the MultiSelect parameter for GetOpenFilename(), the user can select multiple files from the same directory. The return value is a collection containing all those files. You can then iterate it like this:

Public Sub ImportActiveList()
    Dim FileNames As Variant
    Dim FileName As Variant
    Dim WSNew As Worksheet
    Dim ActiveListWB As Workbook

    ' ask the user for the files to copy the data from
    FileNames = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*),*.xls*", _
                                               Title:="Select Active List to Import", _
                                               MultiSelect:=True)
    If VarType(FileNames) = vbBoolean Then
        If Not FileNames Then Exit Sub
    End If

    ' loop over all files selected by the user and import the desired sheets
    For Each FileName In FileNames
        ' create new worksheet to copy the data to
        ' here you could add a name for the sheet or make sure it is at the right position
        Set WSNew = ActiveWorkbook.Sheets.Add

        ' copy the data
        Set ActiveListWB = Workbooks.Open(FileName)
        ActiveListWB.Sheets("Pens").UsedRange.Copy WSNew.Range("A1")
        ActiveListWB.Close False
    Next FileName
End Sub

The following should be noted as well:

FileName = "False" will only work in an english Excel installation since other languages have other literals for False. Also you won't be able to open a file just named "False" because you cannot distinguish between the filename and the return value for an aborted file dialog (this won't be really a problem in most cases, though...).
You save the return value which is of type Variant in a variable that is of type String. If you change that to be Variant as well you can test if the content is of sub type Boolean and if that boolean evaluates to False. This will avoid all problems mentioned above.
ActiveWorkbook.Close closes the current workbook - which is most times the workbook that was just opened to copy the data from. But assume you halt the code, switch to the consolidated workbook and continue the code: Then the active workbook is now this workbook and it will be closed - without prompting to save!
What you really want is to close the workbook you just opened, so I replaced ActiveWorkbook by ActiveListWB.

Hi thanks but I'm getting a debug error at `If VarType(FileName) = vbBoolean And Not FileName Then` — Vince, May 05 '16 at 11:11
that's great thanks. the only thing I want to change is instead of continually asking the user to select a file for data, to allow them to select multiple files in one go. is this possible as it would save a lot of time especially if there are a lot of them. — Vince, May 05 '16 at 11:39
Updated again. If you need the user to select files from multiple directories you have to present a file open dialog for each directory. For this you can use the previous code before I edited (click the "*edited*" link below the answer). — Leviathan, May 05 '16 at 11:54
thanks so much really appreciate the help. one more question....how can the code be adjusted so that the tab is renamed to the same name as the file? — Vince, May 05 '16 at 12:12
Use the sheets `.Name` property. The order of the sheets can be influenced during creation if you provide additional parameters for `Sheets.Add` — Leviathan, May 05 '16 at 12:18
thanks I tried `ActiveSheet.Name = FileName` however this takes the whole file path and doesn't change the name. would you know how to just select the file name? — Vince, May 05 '16 at 12:29
http://stackoverflow.com/a/1755577 shows an example. Do NOT use the accepted answer there, just the one I linked to. — Leviathan, May 05 '16 at 12:34

score 0 · Answer 2 · answered May 05 '16 at 10:55

I have a macro that could be useful. Transform it to your desires:

Sub agrupar()

Application.EnableCancelKey = xlDisabled
Application.ScreenUpdating = False

ruta = Application.ActiveWorkbook.Path
miarchivo = ThisWorkbook.Name
nombrepestaña = ActiveSheet.Name

ChDrive ruta
ChDir ruta

archi = Dir("*.xl*")

Do While archi <> ""

If archi <> miarchivo Then
Workbooks.Open archi, UpdateLinks:=0, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy after:=ThisWorkbook.ActiveSheet
Next Sheet
Workbooks(archi).Close False
End If

archi = Dir()
Loop

Sheets(nombrepestaña).Select

Application.ScreenUpdating = True

End Sub

VBA Import multiple sheets into Workbook

2 Answers2

Linked