Pasting to Dynamic cell using Offset.

[JohnZ1156]

I have a VBA Macro to paste a range to a specific cell, but now, I would like it to paste that range to the cell 4 rows below the last cell with data in column A.

Here's sort of what I'm talking about.

Before, I specified Cell A23. The last cell in column A with data is A19.

I know that I have to select a cell in column A way down at the bottom (A65536), then End(xlUp).Select
That takes me to Cell A19.
Then, how do I work an Offset(4,0) into this line of code where A23 is?

Sheets("PrintSOF").Range("A23").PasteSpecial xlPasteValues
Sheets("PrintSOF").Range("A23").PasteSpecial xlPasteFormats

 

[MARK858]

paste that range to the cell 4 rows below the last cell with data in column A

Maybe...

    With Sheets("PrintSOF").Cells(Rows.Count, "A").End(xlUp).Offset(4)
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
    End With

You definitely do not need the .Select

BTW 65536 is not the bottom row in Excel 2021

 

[JohnZ1156]

Hi Mark858,
Thank you for your help. I wasn't sure exactly how to combine the Offset command into my existing command.
Works great !!!
And yes, I will increase the last row of 65536.

Thanks again, I appreciate your help.
John

 

[MARK858]

You're welcome

I will increase the last row of 65536.

Just use Rows.Count as I did, it'll find the bottom row on whatever version (even if they ever increase it again)