PDA

View Full Version : Solved: Subtotals



austenr
10-30-2006, 05:16 PM
Not sure where to go with this one. I need to do subtotaling in a worksheet. I need to subtotal every time one column changes and then when another column changes. Is there a way to do it without the subtotal function?

A UDF would work but not the subtotal function because I need to export it to Access and I am not sure if the function would hang on to the parse or not.

Say I want to subtotal column A and column D how would you do that in a function? I want to export values only to Access. Thanks

lucas
10-30-2006, 09:21 PM
you could replace formula's with values before you export to access:
'Remove all formula's from the activesheet
Sub removeFormulas()
With Cells
.Select
.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
Range("A1").Select
End Sub

austenr
10-31-2006, 06:39 AM
Thanks lucas but i have run into another unexpected problem. When i try to apply subtotals to the two columns using the Subtotal function under the Data option on the menu, the first subtotal works fine but when you apply the second subtotal using this method the first one disappears. Anyone have any ideas? Attached is a small sample of my worksheet. The subtotals should be on Account Number and Service Description Thanks.

Shazam
10-31-2006, 06:56 AM
Thanks lucas but i have run into another unexpected problem. When i try to apply subtotals to the two columns using the Subtotal function under the Data option on the menu, the first subtotal works fine but when you apply the second subtotal using this method the first one disappears. Anyone have any ideas? Attached is a small sample of my worksheet. The subtotals should be on Account Number and Service Description Thanks.


Hi austenr,


When applying the second subtotal you need to uncheck:

"Replace current subtotals"

austenr
10-31-2006, 07:01 AM
Thanks. that got it.