I pull data for individual customers from a source system into an Excel
workbook. I have a separate Excel workbook template that contains macros,
which allow me to reformat the data in the source workbook. I am trying to
"add" another macro that will do additional formatting, but when trying to
run the following Macro, I get a runtime error. (In the example provided
below, the source workbook is Jervis detail-1.xls).The error message says:
Runtimme Error '9'
Subscript out of range.
Using Debug, it is the second line of code, which reads:
Active Workbook.worksheets ("Jervis detail-1").Sort.Sortfields.clear
For some reason it appears to not be picking up the correct workbook or
worksheet name.
Appreciate all help.
Sub Sort()
'
' Sort Macro
' Sort the data
'
'
Cells.Select
ActiveWorkbook.Worksheets("Jervis detail-1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets(ActiveWorksheet).Sort.SortFields.Add Key:=Range( _
"A2:A1500"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets(ActiveWorksheet).Sort.SortFields.Add Key:=Range( _
"L2:L1500"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets(ActiveWorksheet).Sort.SortFields.Add Key:=Range( _
"P2:P1500"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets(ActiveWorksheet).Sort.SortFields.Add Key:=Range( _
"X2:X1500"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets(ActiveWorksheet).Sort.SortFields.Add Key:=Range( _
"Y2:Y1500"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets(ActiveWorksheet).Sort
.SetRange Range("A1:AH1500")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("Q:Q").Select
Selection.NumberFormat = "$#,##0.00"
Selection.NumberFormat = "$#,##0.0"
Selection.NumberFormat = "$#,##0"
Columns("R:R").Select
Selection.NumberFormat = "0.00"
Selection.NumberFormat = "0.0"
Selection.NumberFormat = "0"
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.
