Combining multiple worksheets of the same format into a single sheet.

[Johnny_H]

Hi All,

I'm having trouble finding a way to combine multiple worksheets into a single sheet. The sheets are named "1" through to "15" and all have the same format with information contained from column A to M. I would like the information from row A5 onwards to be combined, the individual sheets all contain a different number of data filled rows.

I would ideally like all of the worksheets to be combined in a list into a single sheet ensuring no gaps between the end of one sheet and the start of another.

Any help on this would be much appreciated!

Cheers,

Jon

 

[JLGWhiz]

This assumes that the code will be run from the same workbook that contains sheets 1 thru 15.
The workbook should be saved as a macro enabled workbook, then copy this code to the standard code module.

Sub comb()
Dim sh As Worksheet, ssh As Worksheet, lr As Long
Set sh = Sheets.Add
sh.Name = "Master"
    For i = 1 To 15
        Set ssh = Sheets(CStr(i))
        lr = ssh.Cells.Find("*", ssh.Range("A1"), xlFormulas, xlPart, xlByRows, xlPrevious).Row
        ssh.Range("A5:M" & lr).Copy
        sh.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
    Next
End Sub

 

[Johnny_H]

Worked perfectly!

Thanks very much for your help!

 

[Johnny_H]

Hi again,

If I update the individual worksheets is there a way for this to automatically update the new "Master" sheet created? I.e If a value is changed in one of the worksheets can this change then be reflected in the combined sheet?

Cheers,

 

[rrmando]

Hello. Can you help a VBA rookie out? This almost works for what I need.

"For I = 1 To 15" how can I change this to include all of the named sheets in a workbook? My sheets are named "something 1", "some other thing 2", etc...different names each. This code currently works if I rename my sheets "1", "2", etc.

"sh.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues" how can I make this copy and paste formats as well? I tried adding .PasteSpecial xlPasteFormats at the end but not doing the trick.

All of the sheets contain the same column header names on row 1, how can I get the "Master" sheet to only copy the header row once and not copy it from each of the worksheets multiple times?

Thank you.