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!

 

[Fluff]

In that case it sounds like you might have one or more cells that contain a space or some other non-visible character, in the first blank row (probably in col A).
Another possibility, do you have any formulas that return ""

 

[sungersoo]

I have checked for any cells that might be of cause for my problem. I could not find anything out of the ordinary. I have attached my file to dropbox. Any chance you can take a look to check?

https://www.dropbox.com/s/gwvyiot9cj9u7ib/Sales Tracker - Template - Copy.xlsm?dl=0

 

[Fluff]

I think it might have something to do with the table, but try this

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

 

[sungersoo]

That works perfectly! Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[mumps]

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

Try:

Sub Compile()
' Keyboard Shortcut: Ctrl+Shift+E
    Application.ScreenUpdating = False
    Dim x As Long
    x = 2
    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(x, 1)
        x = x + 1
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[Macropod]

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

sungersoo: You have had two threads running on essentially the same topic, wasting the time of people who are subscribed to one thread and not the other.

Kindly read Mr Excel's policy in Rule 12 against asking questions about the same topic in multiple threads: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html
I have now merged the two threads.

 

[mumps]

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

Please ignore the macro I suggested in Post #26 . Start with a blank "Combined" sheet and the original macro works properly.

Sub Compile()
' Keyboard Shortcut: Ctrl+Shift+E
    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
End Sub