HELP: Compile Sheets Into ONE Tab

[sungersoo]

VBA: Compile 5 out of 10 sheets into 1

Hello!

I have made a macro to basically take 5 data sources and order them into new order by headers. Now I have 5 sheets into a workbook of 10 that is set in the same order with same headers.

Is there a macro out here that would compile the 5 sheets (with same headers) into one sheet? I have tried several online but due to the lines not being static, it would not work.

Thank you very much in advance!

 

[mumps]

Re: VBA: Compile 5 out of 10 sheets into 1

What are the names of the 5 sheets and do you want them combined into a new sheet or simply to copy 4 of the sheets to the first sheet?

 

[sungersoo]

Re: VBA: Compile 5 out of 10 sheets into 1

Combined would be the sheet I would want the following sheets be compiled to:
Import
HG Import
HG Import2
Moose Import
CSFE

 

[mumps]

Re: VBA: Compile 5 out of 10 sheets into 1

I am assuming that the "Combined" sheet is blank and that the 5 sheets have the same headers in the same order.

Sub CombineSheets()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Sheets("Import").UsedRange.Cells.Copy Sheets("Combined").Cells(1, 1)
    For Each ws In Sheets(Array("HG Import", "HG Import2", "Moose Import", "CSFE"))
        ws.UsedRange.Offset(1, 0).Copy Sheets("Import").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[sungersoo]

Re: VBA: Compile 5 out of 10 sheets into 1

That macro got me where it would compile all of them to "Import" instead of "Combined" and also some pieces of data weren't copied over...
@mumps Is there something I am missing?

 

[mumps]

Re: VBA: Compile 5 out of 10 sheets into 1

Try:

Sub CombineSheets()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Sheets("Import").UsedRange.Cells.Copy Sheets("Combined").Cells(1, 1)
    For Each ws In Sheets(Array("HG Import", "HG Import2", "Moose Import", "CSFE"))
        ws.UsedRange.Offset(1, 0).Copy Sheets("Combined").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[sungersoo]

Re: VBA: Compile 5 out of 10 sheets into 1

@mumps

It works perfectly but there's a empty row being created for each sheet compiled.

 

[mumps]

Re: VBA: Compile 5 out of 10 sheets into 1

Where is the empty row?

 

[sungersoo]

Re: VBA: Compile 5 out of 10 sheets into 1

i.e. when hg import gets compiled into COMBINED and then hg import2. There would be a empty row in between hg import and hg import2.

 

[mumps]

Re: VBA: Compile 5 out of 10 sheets into 1

You have headers in row 1. Is this correct? Does your data start in row 2 or row 3?