Importing Multiple Workbooks that have multiple worksheets

[SloanRanger]

I’m trying to import a specific worksheet that appears in multiple (40) workbooks. I’m not fussed on how the worksheets are ordered in the new work book. The following code works but tried to pull all the worksheets and falls over when it finds hidden worksheets. As said I only want one worksheets copying.


Sub CombineWorkbooks()

Dim FName As String, FPath As String
Dim Sheet As Worksheet

Application.ScreenUpdating = False

FPath = Worksheets("Combine Sheets").Cells(2, 2).Value
FName = Dir(FPath & "\*.xls*")

Do While FName <> ""

Workbooks.Open Filename:=FPath & "\" & FName, ReadOnly:=True

For Each Sheet In ActiveWorkbook.Sheets

Sheet.Copy After:=ThisWorkbook.Sheets(1)

Next Sheet

Workbooks(FName).Close
FName = Dir()

Loop

Application.ScreenUpdating = True

End Sub

 

[mumps]

Try this macro. Replace the worksheet name (in red) with the name of the sheet you want to copy.

Sub CombineWorkbooks()
    Dim FName As String, FPath As String
    Dim desWB As Workbook
    Set desWB = ThisWorkbook
    Application.ScreenUpdating = False
    FPath = Worksheets("Combine Sheets").Cells(2, 2).Value
    FName = Dir(FPath & "\*.xls*")
    Do While FName <> ""
        Workbooks.Open Filename:=FPath & "\" & FName, ReadOnly:=True
        Sheets("SheetName").Copy After:=desWB.Sheets(1)
        Workbooks(FName).Close False
        FName = Dir()
    Loop
    Application.ScreenUpdating = True
End Sub

 

[SloanRanger]

Thanks. Tried the code but got an error on desWB.Sheets(1)

 

[SloanRanger]

Forgot to add image

 

[mumps]

Strange!! Could you upload a copy of your destination file and one of your source files to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. If the workbooks contains confidential information, you could replace it with generic data.

 

[SloanRanger]

Ok. I’ll give it a go

 

[SloanRanger]

Box

Box

 

[SloanRanger]

‘Example 1’ is the core data and sheet ‘Summary Export’ is what I’m trying to copy from multiple files. And receive file contains the macro and is the file I’m trying to write to.

 

[GWteB]

Tried the code but got an error on desWB.Sheets(1)

@SloanRanger, just an observation. You didn't copy mumps' code exactly as is. Instead you made a declaration of desWB as worksheet where it should be as workbook, giving you a compile error.

 

[SloanRanger]

Thanks, does exactly what it says on the tin. My mistake. Just need to figure how to give the worksheet the same name as the file that it came from