• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Merge many workbooks into one master book

Sara90

Sara90

New Member
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!
 
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
 
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
 
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.


.
 
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.
 
json said:
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.
Click to expand...


The main workbook is an .xlsx file but all the others are .xls
Is that a problem?
 
Code: 
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!!!
 
Or try this..

Code: 
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
 
@Sara

please try the below code

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
 
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!
 
You must log in or register to reply here.
Back
Top