Importing Multiple Workbooks that have multiple worksheets

SloanRanger

New Member
Joined
Jul 12, 2020
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,311
Try this macro. Replace the worksheet name (in red) with the name of the sheet you want to copy.
Rich (BB code):
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

New Member
Joined
Jul 12, 2020
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
Thanks. Tried the code but got an error on desWB.Sheets(1)
 

SloanRanger

New Member
Joined
Jul 12, 2020
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
Forgot to add image
 

Attachments

  • C71DB8D1-937A-4ECE-8850-A7D2E665699D.jpeg
    C71DB8D1-937A-4ECE-8850-A7D2E665699D.jpeg
    102.8 KB · Views: 9

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,311

ADVERTISEMENT

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

New Member
Joined
Jul 12, 2020
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
‘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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jul 12, 2020
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top