VBA Code to copy Sheet1 to Sheet2 and paste in Row 2

[rrickard]

I need to copy Sheet 1 to Sheet 2 and paste special starting in Row 2 on Sheet 2.

The data on sheet 1 could change (# of rows & columns). I just want the paste special to start in row 2 of Sheet 2 so that row 1 doesn't get overwritten.

If I need to define the columns I probably could, but the row data will expand and shrink over time.

I have been using the below code, but it copies all cells between sheets impacting row 1 on Sheet 2 (which I don't want).

Sub Button1_Click()

Application.ScreenUpdating = False
Sheets("Sheet1").Cells.Copy
Sheets("Sheet2").Cells(1, 1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Sheet2").Cells.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub

 

[footoo]

What if I want to stay on Sheet2 after the copy? Using footoo's code after the script it returns me to Sheet 1 (I have a button on Sheet2 that executes the copy).

Sub Button1_Click()
Application.ScreenUpdating = False
Range([A2], Cells(Rows.Count, Columns.Count)).ClearContents
Sheets("Sheet1").Activate
Range([A1], ActiveSheet.UsedRange).Copy
Sheets("Sheet2").Activate
Cells(2, 1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Cells.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub