What is wrong with this macro?

[david.moore]

Hi all,
My spreadsheet creates an invoice and a batch header to post into our
accounting system.
The macro with which I need help generally works well but cells I60 to
I64 should copy and special paste values only, but the formulae
remains after execution of the macro.
Can someone tell me what is wrong?

The faulty lines are at the beginning of the macro -
Range("A1:J64").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False

Cells I60, I62 and I64 of the 'live' worksheet contain formulae -
I60 =SUM(I34:I58)
I62 =IF(M34="A1",ROUND(I60*0.175,2),0)
I64 =I60+I62

The macro reads as follows -

Sub SaveInvoice()
'
' SaveInvoice Macro
' Macro recorded 14/02/2006 by David Moore
'
' Keyboard Shortcut: Ctrl+s
'
Range("A1:J64").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Sheets("BatchHdr").Select
Rows("13:17").Select
Selection.Insert Shift:=xlDown
Range("A5:A9").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("A13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.Font.Bold = False
Range("D1").Select
Selection.Copy
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A5").Select
Application.CutCopyMode = False
Sheets("Other").Select
Range("B34:F59").Select
Selection.ClearContents
Range("I34:I59").Select
Selection.ClearContents
Range("K34:R34").Select
Selection.ClearContents
ActiveWindow.LargeScroll Down:=-1
Range("I21:I22").Select
Selection.ClearContents
Range("L1").Select
Selection.Copy
Range("L34").Select
ActiveSheet.Paste
Range("I21:I22").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub

 

[Dave Peterson]

Just a guess...

Your code assumes that the correct sheet is the active sheet. Are you sure
you're running the code from the correct sheet?

Maybe adding:

worksheets("nameofsheetwithA1:j64toconverttovalues").select

before doing the copy|paste special would help.

 

[david.moore]

You're quite right; this macro does assume the active sheet is the
correct one and I shall look at amending this. Nevertheless, I am in
the correct sheet when the macro is run. Other formulae in the
worksheet do successfully paste to values.
Regards,
Dave Moore

 

[Dave Peterson]

I don't see anything else in your code that would cause a problem.