CODEWITHSUNDEEP
vbaexcelexcel-2010
LearningMig
LearningMig

Reputation: 1

Copy dynamic ranges and append from one sheet to another

I have 3 data sheets all with the same headers (columns). I need to copy all the data into one sheet. The number of rows will vary for each data sheet each time I run the reports. The number of columns are always constant. How do I copy data from sheet 2 and sheet 3 and append onto sheet 1?

Upvotes: 0

Views: 3159

Answers (1)

Sean McMenemy
Sean McMenemy

Reputation: 46

Assuming you have three sheets named Sheet 1:3, with data in A to C.

Sub combine_Sheets()

'Declare variables
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim sht3 As Worksheet
Dim lastRow As Long

'Set variables
Set sht1 = Sheets("Sheet1")
Set sht2 = Sheets("Sheet2")
Set sht3 = Sheets("Sheet3")

'Copy Sheet 2 to Sheet 1

'Find Last row on Sheet 1
lastRow = Cells(sht1.Rows.Count, "A").End(xlUp).Row

'Copy Sheet 2 data to bottom of Sheet 1
sht2.Range("A2:C" & Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row).Copy _
    Destination:=sht1.Range("A" & lastRow + 1)

'Copy Sheet 3 to Sheet 1

'Find new Last row on Sheet 1
lastRow = Cells(sht1.Rows.Count, "A").End(xlUp).Row

'Copy Sheet 3 data to bottom of Sheet 1
sht3.Range("A2:C" & Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row).Copy _
    Destination:=sht1.Range("A" & lastRow + 1)

sht1.Activate

End Sub

Upvotes: 1

Related Questions