Excel 2002 .. still struggling with VBA for AutoSum!

[Sam40mUK]

Hi All,
I did post about this awhile ago and I am sorry to say my problem is still not resolved
A quick recap - I have varying list of values in a column and I wish to add an AutoSum calculation at the bottom whilst executing a macro.
I currently have the following .. (extract only) :

Application.CommandBars("Standard").Controls("&Autosum").Execute
Application.CommandBars("Standard").Controls("&Autosum").Execute
ActiveCell.Offset(2, 0).Select
Selection.Copy
Range("J60").Select

This code works well in Excel 2000 but 'falls down' in Excel 2002. I have tried a few variations on the AutoSum command but to no avail!
This is so frustrating! Any ideas?
Thanks
S

[brettvba]

What is not working you might have to post all of your code im using 2002
_________________


[ This Message was edited by: brettvba on 2002-05-07 15:30 ]

[Sam40mUK]

Hi Brett,
Thanks for your swift response.
In essence I produce a Pivot Table and copy a specific 'summary block' to the end of the PT. I am looking to total the PT using the AutoSum function, as I explained previously it works fine in Excel 2000 and 'falls over' in Excel 2002.
I list below a greater extract of the code. I am not a programmer but I would welcome any other suggestions.

'Calculate Material Pivot Table
Sheets("Invoice").Select
Range("A35").Select
ActiveSheet.PivotTables("PivotTable2").RefreshTable
Range("L36:O38").Select
Selection.Copy
Range("C36").Select
Selection.End(xlDown).Select
'What if no Materials found?
If ActiveCell.Address = "$C$65536" Then
Plant_Pivot
Else
Mat_Pivot_Cont
End If

End Sub


Sub Mat_Pivot_Cont()
'
' Mat_Pivot_Cont Macro
'Continuation from If Statement in Material_Pivot Macro

ActiveCell.Offset(1, -2).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 3).Select
Application.CutCopyMode = False
Application.CommandBars("Standard").Controls("&Autosum").Execute
Application.CommandBars("Standard").Controls("&Autosum").Execute
ActiveCell.Offset(2, 0).Select
Selection.Copy
Range("J60").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("A35").Select
Range("J67").Select

'Need to move into Plant_Pivot after this!
Plant_Pivot

End Sub

Any other questions, please feel free to ask.
Cheers
S

[brettvba]

doesn't look that good but replace the auto sum command bars with
AutoSum
and insert this sub proceedure
Sub AutoSum()

Dim cel1, cel2
ActiveCell.Offset(-1, 0).Select
cel1 = Selection.End(xlUp).Address
cel2 = ActiveCell.Address
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "=sum(" & (cel1) & ":" & (cel2) & ")"

End Sub

hope this helps someone might be able to tidy up my code a bit for you

[Sam40mUK]

Hi Brett,
Just a vote of thanks for your help.
I have had a opportunity to 'test' it on Excel 2002 at work today and it works fine
You are a * (read star!)
Cheers
S