Column VBA

[Manuel998]

Hi

I would like to have a VBA Please, it needs to copy the data in column I and paste in column J and when i run it every week i want the macro to automatically take the formula in column I and paste it in Column J and so on...

I have the code below but would appreciate if someone can help me with tweaking as i am not sure how to maybe use an offset

Sub Manuel()
    Application.ScreenUpdating = False
    Dim lr As Long, i As Long
    lr = Range("H" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
    Dim rng As Range, rng2 As Range
    Set rng = Range("H" & i & ":H" & i + 4)
   
    rng.Copy
    Range("I" & i).PasteSpecial xlPasteValues
   
   
    i = i + 6
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "complete"
End Sub

 

[AlanSidman]

There are no formulas in Column I to copy. It is only data. Please clarify your request or update your attachment to reflect reality.

Also, what does "and so on..." mean. We are not mind readers here. Please be specific as to your needs.

 

[Manuel998]

There are no formulas in Column I to copy. It is only data. Please clarify your request or update your attachment to reflect reality.

Also, what does "and so on..." mean. We are not mind readers here. Please be specific as to your needs.

Column I does not have formulas in this sheet but would like to use paste formulas rather than values. In essence every week I run the macro I want the formulas in column I to be later in to col J and e.g. in the week after take the range in col J and paste in col k. Hope this makes sense? Thanks in advance.

 

[AlanSidman]

Sub Manuel()
    Dim lr As Long, lc As Long
    lr = Range("B" & Rows.Count).End(xlUp).Row
    lc = Cells(2, Columns.Count).End(xlToLeft).Column
    Range(Cells(3, lc), Cells(lr, lc)).Copy
    Cells(3, lc + 1).PasteSpecial xlPasteFormulas

End Sub

This will only work if there are formulas in the last column to be copied.

 

[Derek McGill]

Would some thing simple like inserting a new col after col B work ?