Tweaking a Macro to combine spreadsheets

[gspurr]

Hi

I am trying to combine the worksheets of about 50 files in a folder into a single workbook. (each file only has one sheet)

I am using the code below (which I got from this site but can't remeber who from to credit - sorry) which works perfectly at bringing in all the cells...

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:\" '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

...my problem is that all the sheets in the workbooks in the folder have the same name and when copied into the new workbook keep the same name which seems to make the workbook unstable so that even if I try and rename manually it falls over and crashes excel.

What I would like to do ideally is tweak this macro so that before each sheet is copied into the new workbook, it is renamed to the value in cell C7 which is unique.

I am sure this should be simple but I can;t make it work!!!!!

Please help

Thanks

Gemma

 

[Whisperer14]

Ws.Name = WS.[C7]

should do the job for you. Just place after the line "For each Ws etc"
HTH

 

[Honeyred]

I would like to use the macro posted here to combine multiple excel files into one workbook with multiple tabs. Fortunately mine are all named differently.

Where do I start to use this macro? I need basic help from square 1 on using this macro.

Thanks

 

[Whisperer14]

Open a brand new workbook with just the one blank worksheet in it (ideally) or else the workbook you wish to copy the others into.
Right click on a sheet tab and select 'View Code', OR press Alt+F11
In the Project window, top left of the 3, right click on any element and choose 'Insert' and from the subsequent dialogue box select 'Module'
Now select all of the code below and press Ctrl+C to copy the code to the clipboard
Back to the new module and paste the code in.
Make sure that the Path is the full path for your collection of files and the files are all in the same place.
Place the insertion point of the cursor(flashing cursor) anywhere within the code and then press F5 to run the macro.
Any problems then post back.

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 = "D:\My Documents\Excel Problems\Holiday" '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