Selecting the next column with a macro

[Rook]

Hi,

I have a spreadsheet with a column of data for each week. Each cel
calculates it's value from an external reference. Every week I wil
need to move the formula's to the next column while leaving just th
values in the first column. I have recorded a macro that starts wit
column B and ends by placing the cursor at the top of column C. I can'
figure out how to setup the macro so that the next time I run it i
will copy the formula's to column D and leave just the values in colum
C. Can anybody help? Here is a copy of the macro as it currentl
stands:

Sub StoreInfo3()
Range("B3:B100").Select
Selection.Copy
Range("B3").Select
ActiveSheet.Paste
Range("B3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("B3").Select
ActiveCell.Offset(0, 1).Select
End Su

 

[JE McGimpsey]

One way:

Public Sub StoreInfo4()
With Cells(1, Columns.Count).End(xlToLeft).Resize(100, 1)
.AutoFill Destination:=.Resize(, 2)
.Value = .Value
End With
End Sub