Consolidate multiples rows in one sheet

Question

Please kindly give me some help, multiples sheets need to copy in one sheet name Report. I am using below code but it's not copy. My excel file contained sheet 1 until sheet 8, column A and Column B rows always change the data.

    Sub Consolidate()

    Dim i As Integer
    
    For i = 1 to Worksheets.Count = 1
    
    Worksheets(1).Select
    Range("A1").Select
    Range(Selection, Selection.End(x1Down)).Select
    Range(Selection, Selection.End(x1TopRight)).Select
    Selection.Copy
    
    Worksheets("Report").Select
    Range("A1048576").Select
    Selection.End(x1Up).Select
    ActiveCell.Offset(1,0).Select
    ActiveSheet.Paste
    
    Next i
    
    End sub

Please check the attached.

enter image description here

Please sheet Report

Line 'For i = 1 to Worksheets.Count = 1' is off. Assuming sheet 'Report' is the right-most tab, suspect that line should be: 'For i = 1 to Worksheets.Count - 1 — Spinner, Nov 05 '21 at 01:41
Line 'Worksheets(1).Select' is also off. Should be: 'Worksheets(i).Select' — Spinner, Nov 05 '21 at 01:44
Please read on [how to avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1), it is a very bad practice and should be avoided unless necessary (which is very rarely the case). And also refer to this [answer on how to get last row](https://stackoverflow.com/questions/38882321/better-way-to-find-last-used-row) — Raymond Wu, Nov 05 '21 at 01:48
@GoalExcel: Are you saying the first image is the an example source and the second image is the target result? — Spinner, Nov 05 '21 at 01:58
Thank you for your replay. I am new vba code, but now pop up message Run time error 1004 Application defined- or object defined error. Line = Range(Selection, Selection.End(x1Down)).Select — GoalExcel, Nov 05 '21 at 02:01
To Spinner , first image is the source data example, the second image is the target result, keep in mind I have to create sheet 1 until sheet 8, less or more. — GoalExcel, Nov 05 '21 at 02:06
Thank you so much, Spinner for your wonderful and fastest assistance. Amazing Expert Professional this community — GoalExcel, Nov 05 '21 at 05:25

score 1 · Accepted Answer · edited Nov 09 '21 at 10:34

The following is one way to approach what you're trying to do:

Sub Consolidate()

    Dim i%, inTgtCol%, lgLR&

''' Init target column as -1 (so 1st paste below will be to column 1)
    inTgtCol = -1

    For i = 1 To Worksheets.Count: With Worksheets(i)
    
    ''' Process all sheets except Report
        If .Name <> "Report" Then
    
        ''' Get last non-empty cell in column A
        ''' Copy from A1 to corresponding last row in column B
            lgLR = .Range("A" & .Rows.Count).End(xlUp).Row
            .Range("A1", "B" & lgLR).Copy
                
        ''' Increment target column and Paste to Report
        ''' (column 1, column 3, etc., for each sheet copied)
            inTgtCol = inTgtCol + 2
            Worksheets("Report").Cells(1, inTgtCol).PasteSpecial Paste:=xlAll
        
        End If
    
    End With: Next i

End Sub

If the source sheet's data is more than one column the above can be edited to address that. If you need help with that, state how many columns or say 'it's variable' if that's the case.

Thank you so much, Spinner for your wonderful and fastest assistance. Amazing Expert Professional this community — GoalExcel, Nov 05 '21 at 04:19

Consolidate multiples rows in one sheet

1 Answers1