Re: Excel Macros: Sort which is not Worksheet Specific

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Jan 17, 5:08 pm, eleinia <sarah.lar...@xxxxxxxxxxxxx> 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 work***.  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 work*** I created it in,
but I want to copy the template work*** so there is a new work***
for every month.  The sort will not work on the newly copied work***
because the macro uses exact references to the work*** 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("Table1").Sort.SortFields.C­lear

ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.A­dd
_
        Key:=Range("Table1[[#All],[Name]]"), SortOn:=xlSortOnValues,
Order:= _
        xlAscending, DataOption:=xlSortNormal
    With
ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").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.ActiveWork***.ListObjects("Table1").Sort.SortFields.C­
lear


ActiveWorkbook.ActiveWork***.ListObjects("Table1").Sort.SortFields.A­
dd
_
Key:=Range("Table1[[#All],[Name]]"), SortOn:=xlSortOnValues,
Order:= _
xlAscending, DataOption:=xlSortNormal
With
ActiveWorkbook.ActiveWork***.ListObjects("Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
___________________
.

Quantcast