Problem copying formulas using VB

[Felix]

I'm using the below line in my code to copy sheet with fairly complex
formulas to a new sheet. The formulas in the original sheet are feed from a
data set A the new sheet is feed by a dataset B.

Worksheets("A").Cells.Copy Destination:=Worksheets("B").Cells(1, 1)

Most of the time this works fine without problems but at times some users
report that instead of the formulas the cell content copies as values. In
other words the sheet looks the way you expect (including the formating) but
the cells don't have formulas anymore and contain the values from the
original sheet. I have also seen this with varations of the below code. (i.e.
..Paste).

 

[Per Jessen]

Hi

Use PasteSpecial to paste formulas.
Try this:

Worksheets("A").Cells.Copy
Worksheets ("B"), Cells(1, 1), PasteSpecial(Paste:=xlPasteFormulas)

HTH
Per

 

[Felix]

Thanks, I'll try and see if it is more robust.

Hi

Use PasteSpecial to paste formulas.
Try this:

Worksheets("A").Cells.Copy
Worksheets ("B"), Cells(1, 1), PasteSpecial(Paste:=xlPasteFormulas)

HTH
Per