Macro for Sub totaling multiple sheets

[Anthony Lisanti]

I'm having a problem with a sub total macro. I'm in charge of credit
and risk management for a company, and we have about 40 sales agents.
Our trial balance needs to be dumped from our system into excel, then
broken by sales agent.

The problem is each sales agent's aging is not equal, as far as the
rows go, I cant seem to get a Marco to work properly.

I can make a macro say for the first tab, which might have 50 rows of
data, but then the second tab may have 200. The macro was only
recorded for 50 rows.

How can I do a sub total for each sheet, and have it know to process
the sub total only after the end of the data?



Another question, can I dump my raw data, then make a macro for every
change in sales agent, place that data into a new sheet?


This is a very large report that takes me about 2 hours to process.
Any help with a macro would help a lot!

Thank you!

Tony

 

[nayabaz]

Hi Anthony,

U can record the macro and then modify it to actually change the 50
rows with dynamic reading of the excel rows.

U can do it by selecting the A1 and then doing a xlend operation and
then finding the row number and u can have this row number in a
variable which can be used for the subsequent purpose.

Regards

 

[Don Guillett]

As ALWAYS, post your macro for comments and suggestions.
Something like
lr=ws.cells(rows.count,1).end(xlup).row

 

[Anthony Lisanti]

xlend

I'm pretty new at this Macro thing, and I dont think I'd ever need it
other then Auto sub totaling. Here is what I have for the macro. How
would I use this xlend function?



Sub TotalSheet()
'
' TotalSheet Macro
' Total the sheet
'
' Keyboard Shortcut: Ctrl+Shift+W
'
ActiveWindow.SmallScroll Down:=-57
ActiveWindow.ScrollRow = 337
ActiveWindow.ScrollRow = 335
ActiveWindow.ScrollRow = 334
ActiveWindow.ScrollRow = 332
ActiveWindow.ScrollRow = 331
ActiveWindow.ScrollRow = 330
ActiveWindow.ScrollRow = 328
ActiveWindow.ScrollRow = 324
ActiveWindow.ScrollRow = 320
ActiveWindow.ScrollRow = 318
ActiveWindow.ScrollRow = 313
ActiveWindow.ScrollRow = 308
ActiveWindow.ScrollRow = 302
ActiveWindow.ScrollRow = 296
ActiveWindow.ScrollRow = 289
ActiveWindow.ScrollRow = 280
ActiveWindow.ScrollRow = 273
ActiveWindow.ScrollRow = 266
ActiveWindow.ScrollRow = 259
ActiveWindow.ScrollRow = 238
ActiveWindow.ScrollRow = 232
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 217
ActiveWindow.ScrollRow = 209
ActiveWindow.ScrollRow = 199
ActiveWindow.ScrollRow = 190
ActiveWindow.ScrollRow = 180
ActiveWindow.ScrollRow = 170
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 148
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 126
ActiveWindow.ScrollRow = 103
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
Range("A1:N422").Select
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-12
Selection.Subtotal GroupBy:=1, Function:=xlSum,
TotalList:=Array(9, 10, 11, _
12, 13, 14), Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
End Sub

 

[Don Guillett]

Something like this. I did NOT test. Notice the placement of the dots .
in the with statement.

Sub TotalSheet()
' Keyboard Shortcut: Ctrl+Shift+W
for each ws in worksheets
with ws
lr=.cells(rows.count,"a").end(xlup).row
.Range("A1:N" & lr).Subtotal GroupBy:=1, Function:=xlSum, _
TotalList:=Array(9, 10, 11,12, 13, 14), _
Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
end with
next ws
End Sub