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!

 

[sungersoo]

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

@mumps

Correct

 

[mumps]

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

Are there any blank rows between your headers in row 1 and the first row of your data?

 

[sungersoo]

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

There aren't any blank rows.

 

[mumps]

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

In that case I don't know why you're getting a blank row. It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[sungersoo]

Hello,

I am having trouble with a macro that compiles several sheets into one tab. The issue is after each sheet is compiled, there is a row added for each sheet.

Can anyone help with this macro?

Thank you!

    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", "Untracked2"))
        ws.UsedRange.Offset(1, 0).Copy Sheets("Combined").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Next ws
    Application.ScreenUpdating = True

 

[Fluff]

What do you mean by?

there is a row added for each sheet.

 

[sungersoo]

@Fluff

I.E

Sheet 1 has 4 row totals and Sheet 2 also has 4 rows total.

In the compiled sheet, It would add another row which would make it 10 rows total.

 

[Fluff]

Are you getting blank rows between each sheet, on the compiled sheet?

 

[sungersoo]

@Fluff

Yes. A blank row.

 

[Joe4]

I just set up a real simple example that your code would work against, and I am not getting any blank rows.
Usually, that indicates that you have a row at the end of your data that is blank, but might have some formatting or something to that effect, so that it thinks that is really the last row.

Go to the offending sheets, and hit CTRL-END and see where the cursor goes.
Does it go to the last row, or one row past what you think is the last row?
Inspect that row for anything funny. Then try deleting it and see if that resolves it.