Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping through worksheets, sorting and subtotals
I think the lastrow and lastcol variables are not changing because when you
are looping through the worksheets you are not activating them. Try entering wks.activate on the line under your For Each wks in ........ statement. Hope this helps. Bill Horton "Przemek" wrote: Hi, I have a workbook with various worksheets, which I split depending on what is in column C, than next split (based on value in another column), msg-id: Everything it's fine, saves me a lot of time. But at the end I need sort these worksheets (by column E) and subtotal them. Here is my code: Sub SubTotals(WorkbookName As String) Dim rng As Range Dim wks As Worksheet For Each wks In Workbooks(WorkbookName).Worksheets On Error Resume Next lastRow = Cells.SpecialCells(xlLastCell).Row lastCol = Cells.SpecialCells(xlLastCell).Column Set rg = Range("A1", Cells(lastRow, lastCol)) rg.Select rg.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal rg.Select Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(6, 12), Replace:=True, PageBreaks:=False, SummaryBelowData:=True Next wks End Sub The problems is (I suppose) rg object. Macro is selecting right range on first sheet, but on the others variables lastRow and lastCol are not changing at all. Still got values from the first one :( How can I select all rows with data? And second thing, when my macro should subtotal, it's sometimes doing this and sometimes leaving sheet as it was. Przemek |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping through worksheets, sorting and subtotals
You can do all this stuff without selecting ranges (and ranges can only be
selected on the activesheet and sheets can only be selected in the activeworkbook). Option Explicit Sub mySubTotals(WorkbookName As String) Dim wks As Worksheet Dim LastRow As Long Dim LastCol As Long Dim rg As Range For Each wks In Workbooks(WorkbookName).Worksheets With wks LastRow = .Cells.SpecialCells(xlLastCell).Row LastCol = .Cells.SpecialCells(xlLastCell).Column Set rg = .Range("A1", .Cells(LastRow, LastCol)) rg.Sort Key1:=.Range("E2"), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, DataOption1:=xlSortNormal rg.Subtotal GroupBy:=5, Function:=xlSum, _ TotalList:=Array(6, 12), Replace:=True, PageBreaks:=False, _ SummaryBelowData:=True End With Next wks End Sub I also changed the name of the procedure--subtotals is a property in VBA. Przemek wrote: Hi, I have a workbook with various worksheets, which I split depending on what is in column C, than next split (based on value in another column), msg-id: Everything it's fine, saves me a lot of time. But at the end I need sort these worksheets (by column E) and subtotal them. Here is my code: Sub SubTotals(WorkbookName As String) Dim rng As Range Dim wks As Worksheet For Each wks In Workbooks(WorkbookName).Worksheets On Error Resume Next lastRow = Cells.SpecialCells(xlLastCell).Row lastCol = Cells.SpecialCells(xlLastCell).Column Set rg = Range("A1", Cells(lastRow, lastCol)) rg.Select rg.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal rg.Select Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(6, 12), Replace:=True, PageBreaks:=False, SummaryBelowData:=True Next wks End Sub The problems is (I suppose) rg object. Macro is selecting right range on first sheet, but on the others variables lastRow and lastCol are not changing at all. Still got values from the first one :( How can I select all rows with data? And second thing, when my macro should subtotal, it's sometimes doing this and sometimes leaving sheet as it was. Przemek -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping through worksheets, sorting and subtotals
And since the workbook might not be active, the OP will have to add:
workbooks(workbookname).activate wks.activate William Horton wrote: I think the lastrow and lastcol variables are not changing because when you are looping through the worksheets you are not activating them. Try entering wks.activate on the line under your For Each wks in ........ statement. Hope this helps. Bill Horton "Przemek" wrote: Hi, I have a workbook with various worksheets, which I split depending on what is in column C, than next split (based on value in another column), msg-id: Everything it's fine, saves me a lot of time. But at the end I need sort these worksheets (by column E) and subtotal them. Here is my code: Sub SubTotals(WorkbookName As String) Dim rng As Range Dim wks As Worksheet For Each wks In Workbooks(WorkbookName).Worksheets On Error Resume Next lastRow = Cells.SpecialCells(xlLastCell).Row lastCol = Cells.SpecialCells(xlLastCell).Column Set rg = Range("A1", Cells(lastRow, lastCol)) rg.Select rg.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal rg.Select Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(6, 12), Replace:=True, PageBreaks:=False, SummaryBelowData:=True Next wks End Sub The problems is (I suppose) rg object. Macro is selecting right range on first sheet, but on the others variables lastRow and lastCol are not changing at all. Still got values from the first one :( How can I select all rows with data? And second thing, when my macro should subtotal, it's sometimes doing this and sometimes leaving sheet as it was. Przemek -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Subtotals | Excel Discussion (Misc queries) | |||
sorting after subtotals | Excel Discussion (Misc queries) | |||
Looping through Worksheets | Excel Programming | |||
looping through worksheets | Excel Programming | |||
looping through worksheets | Excel Programming |