Merge two sheets into an existing sheet with Macro

[shoemaro]

I need a macro two combine two separate sheets into an existing sheet in the same workbook. Sheet 1 i need all contents copied and pasted into a combined sheet. Sheet two I need all data excluding the top row. For each sheet I need columns A:K. I have a macro that works, but it creates a new sheet for the combined data, I need the combined data to be on an existing sheet in the same workbook. I have included the macro I have been using below. Any help appreciated. Thanks

Sub Collate_Sheets()

Sheets.Add After:=Sheets(Sheets.Count)
Dim wks As Worksheet
Set wks = Sheets(Sheets.Count)
wks.Name = "Sheet3"
With Sheets("Sheet1")
Dim lastrow As Long
lastrow = .Range("K" & .Rows.Count).End(xlUp).Row
.Range("A1:K" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp)
End With
With Sheets("Sheet2")
lastrow = .Range("K" & .Rows.Count).End(xlUp).Row
.Range("A2:K" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1)
End With

End Sub

 

[Stridhan]

Your macro adding a sheet in the beginnig.
Then you set wks to that sheet and name it Sheet3.
In your copydestination you then use that sheet as destination (wks)

To change destination to another existing sheet just change wks.range to e.g. sheets("existing").range
where existing is the name of the sheet you want to copy to.