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.

:beerchug: