Excel Macros: Sort which is not Worksheet Specific
On Jan 17, 5:08*pm, eleinia wrote:
Hi there
First let me warn you that I am not a programmer, so please go easy on
me!
Background:
Each month, I need to run a report on phone usage and put this
information into a table in a worksheet. *I then run a macro which
uses vlookup to insert a column and put the name of each person next
to their phone number in the list. *The last part I want to achieve
with the macro, but have been unable to, is that the table it is
sorted by the name column.
Problem:
The macro works fine when it is run in the worksheet I created it in,
but I want to copy the template worksheet so there is a new worksheet
for every month. *The sort will not work on the newly copied worksheet
because the macro uses exact references to the worksheet and the
table. *Is anyone able to tell me what references I should be using to
achieve what I want to achieve?
I have chunked the problem down and have created a macro which only
sorts, as below:
___________________
Sub SortNames()
'
' SortNames Macro
'
'
ActiveWorkbook.Worksheets("Sheet1").ListObjects("T able1").Sort.SortFields.C*lear
ActiveWorkbook.Worksheets("Sheet1").ListObjects("T able1").Sort.SortFields.A*dd
_
* * * * Key:=Range("Table1[[#All],[Name]]"), SortOn:=xlSortOnValues,
Order:= _
* * * * xlAscending, DataOption:=xlSortNormal
* * With
ActiveWorkbook.Worksheets("Sheet1").ListObjects("T able1").Sort
* * * * .Header = xlYes
* * * * .MatchCase = False
* * * * .Orientation = xlTopToBottom
* * * * .SortMethod = xlPinYin
* * * * .Apply
* * End With
End Sub
___________________
Thanks very much!
Sarah
hello not sure if Table1 will change in your case. but if not,
try:
Sub SortNames()
'
' SortNames Macro
'
'
ActiveWorkbook.ActiveWorksheet.ListObjects("Table1 ").Sort.SortFields.C*
lear
ActiveWorkbook.ActiveWorksheet.ListObjects("Table1 ").Sort.SortFields.A*
dd
_
Key:=Range("Table1[[#All],[Name]]"), SortOn:=xlSortOnValues,
Order:= _
xlAscending, DataOption:=xlSortNormal
With
ActiveWorkbook.ActiveWorksheet.ListObjects("Table1 ").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
___________________
|