Code to amalgamate rows from diff. worksheets into single worksheet ?

[Maverick27]

Hi - i have rows of data in different worksheets (same workbook) which i would like to amalgamate into a single worksheet.

It will save time in copying & pasting.

Any one care to share code to accomplish this.

Thanks in Advance.

 

[JLGWhiz]

Hi - i have rows of data in different worksheets (same workbook) which i would like to amalgamate into a single worksheet.

It will save time in copying & pasting.

Any one care to share code to accomplish this.

Thanks in Advance.

If you want to share which rows of which sheets, I will share the code to amalgamate them to a separate sheet.

 

[Maverick27]

Thanks for lending a helping hand.

The worksheets can have varying no. of rows across 10 cols (starting from col A). All worksheets will have exactly 10 cols.

The worksheets will be labelled Worksheet A, Worksheet B, Worksheet C....etc.

Hope this helps to formulate a code.

 

[JLGWhiz]

The objective is still vague but here is generic code that perhaps you can work with to do what you want. The code assumes row 1 as header row.

Sub amalgamate()
Dim sh As Worksheet, nsh As Worksheet
Set nsh = Sheets.Add(Before:=Sheets(1))
    For Each sh In ThisWorkbook.Sheets
        If nsh.Application.CountA(Range("1:1")) = 0 Then
            sh.Rows(1).Copy nsh.Range("A1")
        End If
        sh.UsedRange.Offset(1).Copy nsh.Cells(Rows.Count, 1).End(xlUp)(2)
    Next
End Sub

 

[Maverick27]

The objective is still vague but here is generic code that perhaps you can work with to do what you want. The code assumes row 1 as header row.

Sub amalgamate()
Dim sh As Worksheet, nsh As Worksheet
Set nsh = Sheets.Add(Before:=Sheets(1))
    For Each sh In ThisWorkbook.Sheets
        If nsh.Application.CountA(Range("1:1")) = 0 Then
            sh.Rows(1).Copy nsh.Range("A1")
        End If
        sh.UsedRange.Offset(1).Copy nsh.Cells(Rows.Count, 1).End(xlUp)(2)
    Next
End Sub

Thanks for figuring it out, code works like a charm !!

 

[JLGWhiz]

Thanks for figuring it out, code works like a charm !!

You're welcome,
regards, JLG