VBA/Excel - Macro to insert columns after last column entry keeping same format/formulas as adjacent columns

[mr600]

Hi there,

I was hoping someone could help me with the following:

I need a macro that will go into given columns (multiple) and insert a column towards the right-hand-side (i.e. next to the last column with data in it), whilst keeping the same format/formulas as the columns to the left. The columns will have given dates and some formulas below - I need to keep these formulas such that the dates/data update automatically.

I need this to be repeated for a variety of tabs with different names.

Are you able to provide a VBA code to help with this please?

Thanks

 

[JLGWhiz]

I am not really sure what you are looking for, but we can start with this and work from there.

Sub t()
Dim lc As Long
With ActiveSheet
    lc = .Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlPrevious).Column
    .Cells(1, lc).EntireColumn.Copy
    .Cells(1, lc + 1).PasteSpecial xlPasteFormulasAndNumberFormats
    .Cells(1, lc + 1).PasteSpecial xlPasteFormats
End With
End Sub

This copies the formulas , number formats and other formatting from the last column with data to the next available column. If the formulas have absolute references (row and column preceded with $ symbol) then those will continue to evaluate the same cells, but if they are relative references then the cells evaluated will change accordingly by one column.