Copy a set Range of Data and paste it 3 spaces below last row (go past all blank lines to find last row)

Hi there, I have a bit of an issue and hoping someone can assist.
Step 1. I need to copy a set range of data (A1:C10)
Step 2. Paste the data of A1:C10) down to the last row + 3 spaces (This would paste the data starting at A14).
Step 3. If i was to execute the Macro again it would take the data (Step 1.) and paste it to the absolute last row +3  (This would paste the data starting at A27).

The problem I have been having is that when it scans down the workbook to look for a place to paste the data it stops at the first row with a space and stops there. I need it to scan down the entire workbook and look for the absolute last row with data in it.
Copy-Data-and-paste-to-last-row--3sp.JPGBook1.xlsm


Sub CopyAndPasteToLastRowPlus3Spaces()

Dim rgDest As Range
Set rgDest = [a1]
If rgDest <> "" Then
     If rgDest.Offset(0, 0) = "" Then
           Set rgDest = rgDest.Offset(0, 0)
     Else
           Set rgDest = rgDest.End(xlDown).Offset(4, 0)
     End If
End If
[a1:c10].Copy
rgDest.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False



End Sub

Open in new window




Any help would be greatly appreciated.
Thanks, Bob J.
Bob JefferyCQOAsked:
Who is Participating?
 
Rgonzo1971Commented:
hI,

Pls try
Sub macro()

Range("A1:c10").Copy
Range("A" & Rows.Count).End(xlUp).Offset(4).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

Open in new window

Regards
0
 
Bob JefferyCQOAuthor Commented:
Amazing! Thank you so much for your help!
Greatly appreciated.

Bob J.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.