Pasting without Absolute Cell References using VBA

[cyclist101]

I have a spreadsheet that I need to update twice a month (continuously adding columns every month) but the last 9 columns need to stay the same the whole time, so I want to use the following code to copy a specific selection and paste it 9 columns before the end of my worksheet, but I can’t figure out how to tell it where to paste if the cell references of the last column will constantly be changing. Please advise.


Sub Macro4()
Columns("R:V").Select
Range("V1").Activate
Selection.Copy

ActiveCell.PasteSpecial Paste:=xlPasteAll

End Sub

 

[Joe4]

Welcome to the Board!

I am not sure your question is quite clear.
Can you give us an example of one of these formulas, where it should be moved to, and what the formula should look like after the move?

 

[cyclist101]

I want to copy the data in Columns R through V and paste it in front of Column W. At that point, the data that was in Column W will now be at Column AB, but then I want to be able to run the Macro again so that the data in Columns R through V pastes in front of Column AB, and so on, so I can run it like twenty times and simply add five columns to the middle every time. Let me know if you still have questions on that. Thank you.

 

[Joe4]

Are they formulas or values being copied over?
Initially, what is the last column with data? Is it W or something else (so we are always inserting the new columns just before the last column with data)?

 

[Joe4]

Initially, what is the last column with data? Is it W or something else (so we are always inserting the new columns just before the last column with data)?

Sorry, looking back at your original post, you did mention that there are always 9 columns at the end that need to be kept.

See if this code does what you want:

Sub MyInsertColumns()

    Dim lc As Long
    
'   Find last column with data (using row 1)
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    
'   Insert five blank columns before last 9 columns
    Range(Cells(1, lc - 8), Cells(1, lc - 4)).EntireColumn.Insert
    
'   Copy values
    Range(Cells(1, lc - 13), Cells(1, lc - 9)).EntireColumn.Copy Cells(1, lc - 8)
    
End Sub

 

[cyclist101]

They're just values. There's a group of columns on the far right side that always needs to be on the far right. There are 9 columns. Right now they start at W and end at AE, so AE is the last column with data.

 

[Joe4]

Not sure if you saw the proposed solution in my last post.

 

[cyclist101]

That worked great! Thank you so much!

 

[Joe4]

You are welcome!