Paste a columns formats and formulas not its values

[pcarmour]

Hi,

After inserting a column using VBA I want to copy the formats and formulas from the column on its left but not any of its values.
Any help as always is really appreciated.

I am working with Windows Home Premium version 6.1.7601 SP 1 Build 7601and Excel version 14.0.6123.5001 (32 bit)

[GarysStudent]

This example copies from column F to column E:

Sub Macro1()
    Columns("F:F").Select
    Selection.Copy
    Range("E1").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Dim rr As Range
    Set rr = Range("F:F").Cells.SpecialCells(xlCellTypeFormulas)
    For Each r In rr
        r.Copy r.Offset(0, -1)
    Next r
End Sub

[SamT]

Sub Macro1()
    Range("F:F").Copy
    
    'Use only one example below
    
    'Use this line to paste formulas and number formats only
    Range("E1").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
    
    'Use these two lines to paste Formulas and all Cell and number formats
    'Note that one Copy will Paste many times.
    Range("E1").PasteSpecial Paste:=xlPasteFormats
    Range("E1").PasteSpecial Paste:=xlPasteFormulas
    
    'And You might want to use:
    'Range("E1").PasteSpecial Paste:=xlPasteColumnWidths
End Sub

[pcarmour]

Hi GarysStudent,

Thank you very much, that works perfectly.

Hi Sam T, Thank you for your help again. Unfortunately I found when trying the different lines your code still pasted the columns values, but not to worry as I have the answer.

Thank you both for your time and expertise.