#1
|
|||
|
|||
One macro to sort individual sheets in a workbook
I have a workbook of 12 sheets, one for each calendar month. The sheets are identical as far as rows and columns are concerned, and the data types in each column are the same in each sheet. I want to write one macro that will sort the data on the sheet that is open at the time. Below is the macro generated through Excel followed by the macro as I have changed it. Needless to say if it worked I wouldn't be asking for help. I get an error '13' "Type Mismatch" the very first line where I use the variable 'sheet'. If I try to dimension sheet as a String I get a compiler error. It's probably a simple thing that I can't see but any help would be appreciated.
Generated Macro code: Sub Sort_Sheet() ' ' Sort_Sheet Macro ' Sorts data on Due Date and then on Account. ' ' Keyboard Shortcut: Ctrl+s ' Range("A1:H35").Select ActiveWorkbook.Worksheets("JAN15").Sort.SortFields .Clear ActiveWorkbook.Worksheets("JAN15").Sort.SortFields .Add Key:=Range("C2:C35"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("JAN15").Sort.SortFields .Add Key:=Range("A2:A35"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("JAN15").Sort .SetRange Range("A1:H35") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("A2").Select End Sub My changes: 'Sub Sort_Sheet() ' ' Sort_Sheet Macro ' Sorts data on Due Date and then on Account. ' ' Keyboard Shortcut: Ctrl+s ' Dim sheet As Worksheet Range("A1:H35").Select ActiveWorkbook.Worksheets(sheet).Sort.SortFields.C lear ActiveWorkbook.Worksheets(sheet).Sort.SortFields.A dd Key:=Range("C2:C35"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets(sheet).Sort.SortFields.A dd Key:=Range("A2:A35"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets(sheet).Sort .SetRange Range("A1:H35") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("A2").Select End Sub |
#2
|
|||
|
|||
Sorry for the delay.
I used your suggesstion and worked perfect. Thank you for the reply
darby |
Tags |
macro, sort worksheet |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Reference another workbook and return values from the same cell across multiple sheets/tabs | lwls | Excel Programming | 5 | 11-08-2014 02:11 PM |
How add 3 footer items to all sheets in workbook? | Mendopaul | Excel | 3 | 12-05-2013 02:32 PM |
Combining 2 workbooks into 1 workbook with TWO sheets | dguenther | Excel | 1 | 10-06-2011 03:25 AM |
macro to transfer data from one workbook to another workbook | virsojour | Excel Programming | 5 | 02-01-2011 08:58 PM |
How do I merge data from one sheet in a workbook out into multiple sheets | nolesca | Excel | 4 | 06-07-2010 08:13 AM |