Macro to Combine Data from all tabs

[stlpony]

Having issues with this macro ... It's copying the headings which is great, but I need it to automatically copy as much data is on each tab, and then move to the next tab. Any tips? Thanks!



Sub CombineAllTabs()
Dim J As Integer
Dim s As Worksheet


On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"


' copy headings
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1:A1")


For Each s In ActiveWorkbook.Sheets
If s.Name <> "Combined" Then
Application.GoTo Sheets(s.Name).[a1]
Selection.CurrentRegion.Select
' Don't copy the headings
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets("Combined"). _
Cells(Rows.Count, 1).End(xlUp)(2)
End If
Next
End Sub

 

[mumps]

Try:

Sub CombineAllTabs()
    Application.ScreenUpdating = False
    Dim s As Worksheet
    Worksheets.Add(before:=Sheets(1)).Name = "Combined"
    Sheets(2).Rows(1).EntireRow.Copy Sheets("Combined").Cells(1, 1)
    For Each s In Sheets
        If s.Name <> "Combined" Then
            s.UsedRange.Offset(1, 0).Copy Sheets("Combined").Cells(Sheets("Combined").Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next s
    Application.ScreenUpdating = True
End Sub

 

[stlpony]

Try:

Sub CombineAllTabs()
    Application.ScreenUpdating = False
    Dim s As Worksheet
    Worksheets.Add(before:=Sheets(1)).Name = "Combined"
    Sheets(2).Rows(1).EntireRow.Copy Sheets("Combined").Cells(1, 1)
    For Each s In Sheets
        If s.Name <> "Combined" Then
            s.UsedRange.Offset(1, 0).Copy Sheets("Combined").Cells(Sheets("Combined").Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next s
    Application.ScreenUpdating = True
End Sub

Perfect ... Thanks!

 

[mumps]

You are very welcome.

 

[exce101]

Try:

Sub CombineAllTabs()
    Application.ScreenUpdating = False
    Dim s As Worksheet
    Worksheets.Add(before:=Sheets(1)).Name = "Combined"
    Sheets(2).Rows(1).EntireRow.Copy Sheets("Combined").Cells(1, 1)
    For Each s In Sheets
        If s.Name <> "Combined" Then
            s.UsedRange.Offset(1, 0).Copy Sheets("Combined").Cells(Sheets("Combined").Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next s
    Application.ScreenUpdating = True
End Sub

Hi,

This is very helpful.

I had some questions on how i could amend this code to copy a particular range from each worksheet e.g. A5 to FU38

Also, is it possible to add the sheet name from which the data is copied into column A of the combined worksheet in each row the data is pasted?

Finally, can i limit the number of worksheets that the data is copied from i.e. either specify a list, provide a number of sheets to copy or perhaps the code can copy all sheets except the last 5 sheets in the workbook. As i want to use this on multiple workbooks, the last option would work best i suppose.

Thanks in advance for your help!

 

[alansidman]

Have you looked at using Power Query. This is a very simple step in PQ which the Excel team developed to do just this.

 

[exce101]

Have you looked at using Power Query. This is a very simple step in PQ which the Excel team developed to do just this.

I tired searching for the steps for power query but wasn't able to get it to work - perhaps due to the way my data is laid out? there are blank rows, grouped rows etc in my data set

 

[exce101]

Hi,

This is very helpful.

I had some questions on how i could amend this code to copy a particular range from each worksheet e.g. A5 to FU38

Also, is it possible to add the sheet name from which the data is copied into column A of the combined worksheet in each row the data is pasted?

Finally, can i limit the number of worksheets that the data is copied from i.e. either specify a list, provide a number of sheets to copy or perhaps the code can copy all sheets except the last 5 sheets in the workbook. As i want to use this on multiple workbooks, the last option would work best i suppose.

Thanks in advance for your help!

^ Bump - if anyone is able to assist with this. Have a work deadline to meet so appreciate any help.

 

[alansidman]

Without seeing your data, it is difficult to provide a PQ solution. If you are interested in a PQ option, suggest you upload a sample file to a third party location such as Dropbox or Box.net. Be sure to show your current data and mock up manually what you want your final report to look like.

 

[exce101]

Hi Alan,

i am unfortunately unable able to upload files as my work blocks that.

Essentially I have rows 4 to rows 38 spanning across columns A to FU with data in a number of tabs that I would like copy over stacked on top of each other in a new worksheet.

There are a couple of black rows in this data sets to separate the the segments.

How could I use PQ to combine this data into a single sheet while maintaining the formats?

Apologies if I am not clear with the ask.