View Full Version : [SOLVED:] Paste a columns formats and formulas not its values
pcarmour
09-26-2013, 02:17 PM
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
09-26-2013, 04:58 PM
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
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
09-26-2013, 11:49 PM
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:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.