Merge many workbooks into one master book

[Sara90]

I have 244 separate excel workbooks on my mac and I need them to all be in one master workbook. Like, a separate sheet for each one in one workbook. I have asked around but VBA codes that people have suggested have not worked. Please help!

 

[json]

I'm not strong enough in VBA to assist with that route..... but there's always Copy & Paste?! hahahha Just Kidding of course!


just to confirm: In these 244 workbooks... do they all have 1 worksheet? are they named or are they all "sheet1"?

also, depending on how much data is in each sheet... this can potentially result in MASSIVE file

 

[Deepak]

Pls share the sample workbook to assist you better!!!

 

[Sara90]

Each workbook has one sheet each workbook is named, 1-Cons, 2-Cons, etc. They are all in the same folder on my desktop.

 

[Sara90]

I have 244 of these files and I do not want to move them by hand

 

[Sara90]

Most suggest this:

Sub GetSheets()
Path = "C:/Users/Sara/Desktop/FLAVA Database 2"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

But I keep getting a message "path not found" and it highlights the third line

 

[bobhc]

Have you put YOUR Dir/path in? And the correct file name?

If you want VBA you would get better help if you posted in the VBA section of the forum.


.

 

[json]

just saw something, Sara...

the VBA code may be looking for files with the .xls extension, but 1 of the files that you uploaded was an .xlsx
again, I could be swinging in the wrong direction, but i do know vba can be very specific and therefore very tricky.

 

[Sara90]

just saw something, Sara...

the VBA code may be looking for files with the .xls extension, but 1 of the files that you uploaded was an .xlsx
again, I could be swinging in the wrong direction, but i do know vba can be very specific and therefore very tricky.

The main workbook is an .xlsx file but all the others are .xls
Is that a problem?

 

[Sara90]

Have you put YOUR Dir/path in? And the correct file name?

If you want VBA you would get better help if you posted in the VBA section of the forum.


.

Yes I did, and Okay thank you!

 

[Deepak]

Change this for "path not found" error!!!
Path = "C:\Users\Sara\Desktop\FLAVA Database 2\"

or
Path = CreateObject("Wscript.Shell").SpecialFolders("Desktop") _
    & Application.PathSeparator & "FLAVA Database 2\"

'& Recheck, is your folder name is correct!!!

 

[Deepak]

Or try this..

Sub GetSheets1()
Dim path As String
path = CreateObject("Wscript.Shell").SpecialFolders("Desktop") & Application.PathSeparator & "FLAVA Database 2\"
Filename = Dir(path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close False
Filename = Dir()
Loop
End Sub

 

[sgmpatnaik]

@Sara

please try the below code

Option Explicit
Sub CombineFiles()
   
  Dim Path  As String
  Dim FileName  As String
  Dim Wkb  As Workbook
  Dim WS  As Worksheet
   
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  Path = "C:\Users\user\Desktop\Sara" 'Change as needed
  FileName = Dir(Path & "\*.xls", vbNormal)
  Do Until FileName = ""
  Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
  For Each WS In Wkb.Worksheets
  WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
  Next WS
  Wkb.Close False
  FileName = Dir()
  Loop
  Application.EnableEvents = True
  Application.ScreenUpdating = True
   
End Sub

if any problem please inform

Thanks

SP

 

[SirJB7]

Hi, Sara90!

Just passing by and I agree with Deepak's suggestion: at least, there's an Application.PathSeparator missing in your posted code. I didn't tested it nor analyzed sgmpatnaik's code.

Try both alternatives and check if any of them works fine. Then come back here and tell us.

Regards!