PDA

View Full Version : Creating a macro to copy formulas



chownie20
01-23-2009, 06:35 AM
hi all, looking for some help.....not too clued up on macros!.....

I want a macro to copy this exact formula (as shown in the txt bar, not changing the cell references as it does it) across to the 7 cells directly to the right of the current selected cell.

not sure how to make it copy the formula and paste it as it stands, rather than changing it with the cell references within it


iv only got this so far....but it doesnt work :s




Sub AutoShape11_Click()

ActiveCell.Select
ActiveCell.Copy


ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
End Sub

nst1107
01-23-2009, 08:47 AM
Yeah...you're way off. Is it important that the formula is preserved as you copy over, or do you just want the value of the active cell?

mdmackillop
01-23-2009, 08:56 AM
Sub CopyFormula()
Dim fmla As String
fmla = ActiveCell.Formula
ActiveCell.Offset(5, 4).Formula = fmla
End Sub