troubleshooting Question

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

Avatar of Bob Jeffery
Bob JefferyFlag for Canada asked on
Microsoft ExcelMicrosoft Office
2 Comments1 Solution125 ViewsLast Modified:
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

Any help would be greatly appreciated.
Thanks, Bob J.
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros