VBA Code - Copy set range and paste in the next blank row (skipping 1 row)

[Franchise1979]

Hi All, I have a very small and easy spreadsheet in which I am trying to do a macro for the following:

1) Copy cells B5:J19 (including formats)
2) find the next row with no data
3) paste in the next row with no data (but skipping 1 more row so there is a space)

Here is the code I am using:

Sub PasteSource()
  Application.ScreenUpdating = False
  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet

  Set copySheet = Worksheets("Sheet1")
  Set pasteSheet = Worksheets("Sheet1")

  copySheet.Range("B5:J18").Copy
  pasteSheet.Cells(Rows.Count, 6).End(xlUp).Offset(2, 0).PasteSpecial xlPasteValues
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

The offset 2 I know is adding the extra row I need but for some reason, it is pasting the data (values only, which I know I have PasteValues in the code) 4 columns over to the right.

Notes:
1) I am perfectly fine copying the entire rows (5-19) vs a range
2) The range will always be pasted in the same worksheet
3) Worksheet name could change (so thinking "sheet1" needs to be simply active sheet so I remove sheet naems out of the equation?

Any and all help is appreciated.

Thank you!!!!!

 

[Peter_SSs]

Does that make more sense explaining it that way?

Yes, thanks. Try this version (still uses the clearing)

Sub CopyBlock_v4()
  Dim rTopLeft As Range
 
  Set rTopLeft = Range("F" & Rows.Count).End(xlUp).Offset(2, -4)
  Range("B5:J18").Copy Destination:=rTopLeft
  Union(rTopLeft.Resize(14, 4), rTopLeft.Offset(, 5).Resize(14, 3)).ClearContents
End Sub