Combine several sheets to one sheet

harsha6989 · Jul 6th 2018

Hi I have multiple sheets in one workbook and I need combined data in one separate sheet. My data is from AA to AZ and all are dynamic rows. Please help.

Mumps · Jul 6th 2018

Is the destination sheet in the same workbook? What is the name of the destination sheet? Do you want to copy the entire sheet including any headers you might have? Are there any other sheets in the workbook besides the destination sheet and the sheets you want to copy?

harsha6989 · Jul 7th 2018

I am ok with the both the things. If I get a new separate workbook it will be good & Yes all sheets has headers. Scenario is I have a workbook which has almost 8 sheets and its dynamic. I have to combine the data in that sheet from AA:AY and all are dynamic rows.

Mumps · Jul 7th 2018

I still need to know the name of the sheet where you want to combine the data and if there a[SIZE=13px]re any other sheets in the workbook besides the sheet [/SIZE][SIZE=13px]where you want to combine the data [/SIZE][SIZE=13px]and the sheets you want to copy? [/SIZE]

harsha6989 · Jul 8th 2018

Hi I have attached sample workbook. I need all Team 1.....Team n(dynamic) sheets data in combined sheet.. All data is dynamic.

Mumps · Jul 8th 2018

Try:

Code

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim ws As Worksheet
    Sheets("Team 1").Rows(1).EntireRow.Copy Sheets("Combined").Cells(1, 1)
    For Each ws In Sheets
        If ws.Name <> "Combined" Then
            LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            ws.Range("A2:Y" & LastRow).Copy Sheets("Combined").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

Display More

harsha6989 · Jul 8th 2018

Hi Thank you so much for the help its working perfectly. I have a small question what if the sheet names are different? and also it would be easy if it auto creates a new sheet "Combined" and do the consolidation thing.

Mumps · Jul 8th 2018

The sheet names don't matter. Try:

Code

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim ws As Worksheet
    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = "Combined"
    Sheets(2).Rows(1).EntireRow.Copy Sheets("Combined").Cells(1, 1)
    For Each ws In Sheets
        If ws.Name <> "Combined" Then
            LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            ws.Range("A2:Y" & LastRow).Copy Sheets("Combined").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

Display More

harsha6989 · Jul 9th 2018

Awesome thank you so much..This is more than what I wanted.Thanks a lot

Mumps · Jul 9th 2018

You are very welcome.

Combine several sheets to one sheet

Participate now!

Share