Bob Jeffery
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.
Book1.xlsm
Any help would be greatly appreciated.
Thanks, Bob J.
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.
Book1.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
Any help would be greatly appreciated.
Thanks, Bob J.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Greatly appreciated.
Bob J.