VBA to drag formulas into next blank column

[Kieran1995]

Hi,

I'm looking to use a macro to drag formulas from the end column to the next blank column and then paste values on the previous column.

For example, column V contains the formulas at first. I want to then drag these formulas into column W and Paste Values in column V, but I want to write the code in a way that when it comes to using the macro again the formulas that are now in column W will be dragged into column X and then have their values pasted in column X.

This is what I've got so far:

Sub RollFile()

    Columns("V3:V114").Select
    Selection.AutoFill Destination:=Columns("V3:W114"), Type:=xlFillDefault
    Columns("V3:V114").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("W2").Select
    
End Sub

Any help would be great.

 

[Fluff]

Hi & welcome to the board.
If it's always the last col you want to copy try

Sub RollFile()
   Dim UsdCols As Long
   
   UsdCols = Cells(3, Columns.Count).End(xlToLeft).Column
   With Range(Cells(3, UsdCols), Cells(114, UsdCols))
      .Copy .Offset(, 1)
      .Value = .Value
      .Offset(-1, 1)(1).Select
   End With
    
End Sub

 

[Kieran1995]

Perfect, thanks for your quick response!!

 

[Fluff]

Glad to help & thanks for the feedback