Yeah, that's the trouble with recording Macros - It assumes everything will be the same, every time.
So, yes different lengths of the Column data etc will matter, and you're using a CSV file for your data - Sheet 1 tends to get named the same as your file name which also won't help.
Assuming you recorded the Macro you'd have had something like this;
Sub Disloe()
Range("C:C,E:E,F:F,G:G").Select
Range("G1").Activate
Selection.Delete Shift:=xlToLeft
Range("C1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$C$1419").AutoFilter Field:=3, Criteria1:= _
"Long tail"
Columns("A:C").Select
ActiveWorkbook.Worksheets("WC2-A").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("WC2-A").Sort.SortFields.Add Key:=Range("A2:A1419") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("WC2-A").Sort.SortFields.Add Key:=Range("B2:B1419") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("WC2-A").Sort
.SetRange Range("A2:C1418")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
As you can see you have 'hard coded' Ranges (A2:C1419) which are going to be different and you have the Worksheet name such as "WC2-A" hardcoded - Excel will expect them every time, otherwise it'll give you the 'Subscript out of range' error.
So, to cut a long story short you need to help Excel understand the differences, and so...
Sub Disloe2()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Sheet1.Name = "Disloe"
Range("C:C,E:E,F:F,G:G").Delete
Range("C1").AutoFilter
ActiveSheet.Range("$A$1:$C$" & LastRow).AutoFilter Field:=3, Criteria1:="Long tail"
ActiveWorkbook.Worksheets("Disloe").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Disloe").Sort.SortFields.Add Key:=Range("A2:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Disloe").Sort.SortFields.Add Key:=Range("B2:B" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Disloe").Sort
.SetRange Range("A2:C" & LastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
I've used a variable (LastRow) to hold the number of rows of the current data, and I've renamed the Sheet from the very start.
The variable then takes the place of the last row number in the code, and I can refer to the sheet I know will exist because I named it as such.
'Disloe2' - Should work with all your files - HOWEVER - You might need to change the Filter Criteria to what you need it to be - You didn't say so I just choose something,
Post back if you need more help
Bookmarks