macro in excel 2007

  • Thread starter Thread starter Angie
  • Start date Start date
A

Angie

I have a workbook with mutliple worksheets, I create a macro to sort and
subtotal data in a worksheet, but when I apply the macro to another
worksheet, it does not work. When I look at the program, it looks like the
macro is worksheet specific. Can someone help?
 
Angie
You say: "When I look at the program, it looks like the macro is worksheet
specific." What do you mean by "program"? What indicates that the macro is
worksheet specific? Is the macro in a sheet module or in a regular module?
Post the macro itself. HTH Otto
 
This is the code. Under the Activeworkbook.worksheets"smith", smith is the
name of the worksheet.

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+a
'
Range(Selection, Selection.End(xlToRight)).Select
ActiveCell.Range("A1:AD1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:AD30").Select
ActiveWorkbook.Worksheets("smith").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("smith").Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 24).Range("A1:A24"), SortOn:=xlSortOnValues,
Order:=xlDescending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("smith").Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 3).Range("A1:A24"), SortOn:=xlSortOnValues,
Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("smith").Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 6).Range("A1:A24"), SortOn:=xlSortOnValues,
Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("smith").Sort
.SetRange ActiveCell.Offset(-1, 0).Range("A1:AD25")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.Subtotal GroupBy:=25, Function:=xlSum, TotalList:=Array(11,
12, _
13, 14, 15, 16, 17, 18, 19, 20, 21, 24), Replace:=True,
PageBreaks:=False, _
SummaryBelowData:=True
Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(11, 12,
13 _
, 14, 15, 16, 17, 18, 19, 20, 21, 24), Replace:=False,
PageBreaks:=False, _
SummaryBelowData:=True

End Sub
 
1) I'd recommend not using CTRL a for a shortcut. That's used for something
else
2) What exactly do you want to sort and how do you want to start?

I did a little bit of cleanup here and this assumes that the activesheet is
smith when it was recorded. I'd make more changes, but that's for someone
else. :)

Option Explicit

Sub Macro1()
'
' Macro1 Macro
'
Dim aWB As Excel.Workbook
Dim aWS As Excel.Worksheet

Set aWB = ActiveWorkbook
Set aWS = ActiveSheet 'Assume activesheet name is "smith"

aWS.Range(Selection, Selection.End(xlToRight)).Select
ActiveCell.Range("A1:AD1").Select
aWS.Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:AD30").Select
aWS.Sort.SortFields.Clear
aWS.Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 24).Range("A1:A24"), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal
aWS.Sort.SortFields.Add _
Key:=ActiveCell.Offset(0, 3).Range("A1:A24"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
aWS.Sort.SortFields.Add _
Key:=ActiveCell.Offset(0, 6).Range("A1:A24"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
With aWS.Sort
.SetRange ActiveCell.Offset(-1, 0).Range("A1:AD25")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.Subtotal _
GroupBy:=25, _
Function:=xlSum, _
TotalList:=Array(11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 24), _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True
Selection.Subtotal _
GroupBy:=4, _
Function:=xlSum, _
TotalList:=Array(11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 24), _
Replace:=False, _
PageBreaks:=False, _
SummaryBelowData:=True

End Sub


HTH,
Barb Reinhardt
 
1. Ok, I will use CTRL b. Thanks for the recommendation.
2. I sort three columns, 1st sort is status of an account (ie. include or
exclude) in descending order, 2nd sort is of type of account in ascending
order, 3rd sort is date of account in ascending order. I always start at
cell A6, highlight the whole range of data in each tab and sort. Since each
tab has different rows of data, in order to cover all rows, I use the number
of rows of the longest data.

When you say assume the active sheet is "smith", does this macro work for
other tab in the workbook? Because each worksheet in the workbook has a
different name, but I want to perform the same sort and subtotal using the
macro. This will save me lot of time.
 
After Set aWS = ActiveSheet, put this

aWS.Range("A6").select

I really don't like using select, but I don't have time to clean up all of
the selects in your code. You may want to save a backup copy of the
workbook just in case it doesn't do what you want.

HTH,
Barb Reinhardt
 
CTRL + b is used to Bold cells.

If you don't use the Bolding much, no big deal.


Gord Dibben MS Excel MVP
 
Back
Top