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

 

[rrickard]

I need help copying from Sheet 1 to Sheet 2 and paste special starting in row 2. Row 1 (on sheet 1) has data which I want to be copied, but I need the entire sheet to be pasted starting in row 2 of sheet 2. Essentially shifting it all down one row.

 

[rrickard]

I should add, I have used the below code which works pretty good. But I have some static data in row 1 of Sheet 2 that I don't need overwritten with the copy.

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]

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

 

[steve the fish]

Here would be a way to do that. I think i would rather just paste the correct amount of data though.

Sheets("Sheet1").Cells.Resize(Rows.Count - 1).Copy
Sheets("Sheet2").Cells(2, 1).PasteSpecial xlPasteValues

 

[footoo]

This is the same question you asked here:
https://www.mrexcel.com/forum/excel...-sheet1-sheet2-paste-row-2-a.html#post4876013

Did you try what I suggested?

 

[rrickard]

Thanks Steve! This works great! Now I have a couple of options!

 

[rrickard]

Thanks! This works perfectly!!!

 

[Joe4]

I have merged your two threads together.

In the future, please be careful not to post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #12 here: Forum Rules).

Note that sometimes posts from new users require Moderator approval before you can see them (you will see a message to this effect). Please be patient for that to happen (don't try posting the question again).

Thank you.

 

[rrickard]

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).