Sorting Trouble

[wtnelso]

Hello, I'm having trouble sorting the data on multiple worksheets in a work book. I want to cycle through each WS in my array and sort the data in column A:A.

Dim WS As Variant
For Each WS In Array("FY07 Report", "FY08 Report", "FY09 Report")
Worksheets("" & WS & "").Range("A:A").Sort.SortFields.Add order1:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom, SortMethod:=xlPinYin
Next

The error say: "Unable to get the sort property of the range class".

Thanks!

 

[hiker95]

wtnelso,


Sample worksheets:

Excel Workbook
	A
1	Header
2	10
3	9
4	8
5	7
6	6
7	5
8
FY07 Report

Excel Workbook
	A
1	Header
2	10
3	9
4	8
5	7
6	6
7	5
8	4
9	3
10	2
11
FY08 Report

Excel Workbook
	A
1	Header
2	10
3	9
4	8
5	7
6	6
7	5
8	4
9	3
10	2
11	1
12
FY09 Report

After the macro:

Excel Workbook
	A
1	Header
2	5
3	6
4	7
5	8
6	9
7	10
8
FY07 Report

Excel Workbook
	A
1	Header
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11
FY08 Report

Excel Workbook
	A
1	Header
2	1
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12
FY09 Report

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub SortSheets()
' hiker95, 06/19/2012
' http://www.mrexcel.com/forum/showthread.php?641525-Sorting-Trouble
Dim WSary As Variant, ws As Worksheet, i As Long, lr As Long
Application.ScreenUpdating = False
WSary = Array("FY07 Report", "FY08 Report", "FY09 Report")
For i = LBound(WSary) To UBound(WSary)
  Set ws = Worksheets(WSary(i))
  lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
  ws.Range("A2:A" & lr).Sort key1:=ws.Range("A2"), order1:=1
Next i
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the SortSheets macro.