PDA

View Full Version : VBA Macro: sorting in Column A


amitash
08-29-2012, 01:47 AM
Hi ALL,
I want to write a macro that can sort the column A (and other columns' values will be sorted accordingly) in whatever worksheets. I try to record the sorting and edit the code. But the problem I face is I don't know what to put inside the ListObjects(), any idea?


Range("A2").Select
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table_owssvr_1279").Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table_owssvr_1279").Sort. _
SortFields.Add Key:=Range("A2"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(wsName).ListObjects("Table_owssvr_1279").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Bob Phillips
08-29-2012, 02:29 AM
You put the name of the table there. I cannot see where you are struggling.
amitash
08-29-2012, 02:56 AM
Hi xld, I just record the macro, the name of the table is not put by me. Also, I want to have the sorting apply for whatever worksheets in which I don't know the "table" name ...
Bob Phillips
08-29-2012, 04:28 AM
Assuming only one table per sheet, you could use

.ListObjects(1)

everywhere
amitash
08-29-2012, 07:06 PM
Hi xld, I tried with .ListObjects(1), but with "Run-time error '9'; Subscript out of range" ...
Bob Phillips
08-30-2012, 12:13 AM
Can you post the workbook with the code you changed?
amitash
08-30-2012, 03:02 AM
Hi, I tried below code:


Range("A2").Select ActiveWorkbook.Worksheets("Sheet1").ListObjects(1).Sort. _ SortFields.Clear ActiveWorkbook.Worksheets("Sheet1").ListObjects(1).Sort. _ SortFields.Add Key:=Range("A2"), SortOn:=xlSortOnValues, Order:= _ xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets(wsName).ListObjects(1).Sort .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With
Bob Phillips
08-31-2012, 01:07 AM
You aren't qualifying the Range object in the SortFields.Add, and you also reference Sheet1 at one point, and via a variable wsName at another point. wsName may not be initialised.

This layout may show it better

Range("A2").Select
With ActiveWorkbook

With .Worksheets("Sheet1")

.ListObjects(1).Sort.SortFields.Clear
.ListObjects(1).Sort.SortFields.Add Key:=.Range("A2"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
End With

With .Worksheets(wsName).ListObjects(1).Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With