copy all sheets

ACCA · Jun 2, 2020

I want to copy all data from other sheets to active sheet , all data are arranged in Column A & B, but range are different in each each sheet i.e in "sheet 1" data range from "A2:B200" and in "sheet 2" data range from "A2:B400".

Logit · Jun 2, 2020

.

Code:

Sub CpyAllSheets()


Dim WS1, WScur As Worksheet
Dim i As Long
Dim lrow1, lrow As Variant


Set WS1 = Sheets("Sheet1")


WS1.Cells.ClearContents     '<----- this clears Sheet1 before pasting new data from other sheets.


For i = 2 To ActiveWorkbook.Worksheets.Count
        lrow1 = WS1.Cells(Rows.Count, 1).End(xlUp).Row + 1
        Set WScur = Sheets(i)
        lrow = WScur.Cells(Rows.Count, 1).End(xlUp).Row
        
        WScur.Range("A2:Z" & lrow).Copy WS1.Range("A" & lrow1) '<--- change range "A2:Z" as required
        
        
    
Next i


End Sub

copy all sheets

ACCA

New member

Logit

Member