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
_________________<MARQUEE scrollamount=8 behavior=alternate>

</MARQUEE>
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

 

[Gaura215]

Hello

I have a similar problem, but I think I need a bit difference in code.

I have a data, in which I make 3 different tables, for all tables i need auto sum at the end of each table in colum I, J, K, L, M & N. But the problem in my data is that, there may be blanks in the tables in between the rows. Please help me out.

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