Copy formula from a one sheet to another when running a macro

[lewis94]

I have a macro that pulls a column from one worksheet to another, formats it etc but it is not copying over the formula

For example, on the column I am copying it has a cell with the content of +Y4/Y2, and I need this to also transfer over but with the sheet name to refer it back to the original sheet.

Regards

 

[04nimmot]

Perhaps you are after something a tad more complex, but the simplest method would be to use absolute references and sheet names in the original formula.
=+(Sheet1!$W$4/Sheet1!$W$2)

Then you can simply paste special formulas as below.

Sub CopyFormula()
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

 

[lewis94]

Thank you for your reply.

I pull the sheet name from an inout box using "Trim(arr(0)"

I have used the following but if I add in the formula element it crashes and requires debug

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

 

[04nimmot]

My understading of PasteSpecial is that it can perform only one core function at a time (formula or formats) try the below:

Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False