Avatar of Bob Jeffery
Bob Jeffery
Flag for Canada

asked on 

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.

Sub CopyAndPasteToLastRowPlus3Spaces()

Dim rgDest As Range
Set rgDest = [a1]
If rgDest <> "" Then
     If rgDest.Offset(0, 0) = "" Then
           Set rgDest = rgDest.Offset(0, 0)
           Set rgDest = rgDest.End(xlDown).Offset(4, 0)
     End If
End If
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.
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Bob Jeffery

8/22/2022 - Mon