Macro to sort across multiple sheets and remove duplicates

[derek.hughes]

I have a workbook with multiple sheets of data and all are in the same format (data with the same headers in columns A:I). However, the number of rows with data is a variable - different for each sheet.

I want it to sort column A ascending and column I descending, and then remove duplicates - for each sheet in the workbook. I keep getting a debug on this line:
".SetRange ActiveCell.Offset(-1, 0).Range("A1:I731")"

How do I resolve this? Thanks!

Derek

Sub Sort()'
' Sort Macro
'


'
Dim wsheet As Worksheet


For Each wsheet In ActiveWorkbook.Worksheets
Sheets(wsheet.Name).Select
    ActiveCell.Cells.Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=ActiveCell.Range( _
        "A1:A730"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveSheet.Sort.SortFields.Add Key:=ActiveCell.Offset( _
        0, 8).Range("A1:A730"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange ActiveCell.Offset(-1, 0).Range("A1:I731")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Range("$A$1:$I$731").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveCell.Offset(-1, 0).Range("A1").Select


Next wsheet


End Sub

 

[hiker95]

derek.hughes,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


IF I understand you correctly.


Sample worksheets before the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	A	B	C	D	E	F	G	H	I
2	5								1
3	4								2
4	5								3
5	2								4
6	1								5
7
Sheet1

Excel 2007
	A	B	C	D	E	F	G	H	I
1	A	B	C	D	E	F	G	H	I
2	3								1
3	2								2
4	3								3
5
Sheet2

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	A	B	C	D	E	F	G	H	I
2	1								5
3	2								4
4	4								2
5	5								3
6
7
Sheet1

Excel 2007
	A	B	C	D	E	F	G	H	I
1	A	B	C	D	E	F	G	H	I
2	2								2
3	3								3
4
5
Sheet2

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).

Sub Sort_V2()
' hiker95, 11/02/2014, ME815470
Dim ws As Worksheet, lr As Long
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
  lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
  ws.Range("A2:I" & lr).Sort key1:=ws.Range("A2"), order1:=1, key2:=ws.Range("I2"), order2:=2
  ws.Range("A2:I" & lr).RemoveDuplicates Columns:=1, Header:=xlYes
Next ws
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 Sort_V2 macro.