Excel Macros: Sort which is not Worksheet Specific

E

eleinia

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("Table1").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Add
_
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
 
G

GTVT06

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("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
Click to expand...
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
___________________
 
J

Jim Cone

I assume you are using XL2007? - "Sort.SortFields" ???
As I don't use xl2007 - the following may work or it may not...
'--
Replace:
ActiveWorkbook.Worksheets("Sheet1")
With:
ActiveSheet
'--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



in message
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("Table1").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1").Sort.SortFields.Add
_
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
 
E

eleinia

Hi - thanks for the replies! Yes, I am using XL2007.

I replaced ActiveWorkbook.Worksheets("Sheet1") with ActiveSheet as
suggested, and the macro still worked on the original worksheet.
However it still references Table 1, and the name of the table will
change incrementally each time the worksheet is copied, so it did not
work on the new worksheet.

Any ideas on the correct way to reference that:

The new macro is as follows:

Sub Macro19()
'
' Macro19 Macro
'

'
ActiveSheet.ListObjects("Table1").Sort.SortFields.Clear
ActiveSheet.ListObjects("Table1").Sort.SortFields.Add _
Key:=Range("Table1[[#All],[Name]]"), SortOn:=xlSortOnValues,
Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveSheet.ListObjects("Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Cheers
Sarah

I assume you are using XL2007? - "Sort.SortFields" ???
As I don't use xl2007 - the following may work or it may not...
'--
Replace:  
   ActiveWorkbook.Worksheets("Sheet1")
With:
   ActiveSheet
'--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

in message

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("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
Click to expand...
 
J

Jim Cone

I suspect that you can change...
ActiveSheet.ListObjects("Table1")
To...
ActiveSheet.ListObjects(1)
and have it work.

However...
Key:=Range("Table1[[#All],[Name]]") ...will probably take some work???
I am not the one to help with that.

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins - check out "Special Sort")




"eleinia"
wrote in message
Hi - thanks for the replies! Yes, I am using XL2007.

I replaced ActiveWorkbook.Worksheets("Sheet1") with ActiveSheet as
suggested, and the macro still worked on the original worksheet.
However it still references Table 1, and the name of the table will
change incrementally each time the worksheet is copied, so it did not
work on the new worksheet.

Any ideas on the correct way to reference that:
The new macro is as follows:

Sub Macro19()'
' Macro19 Macro
'
ActiveSheet.ListObjects("Table1").Sort.SortFields.Clear
ActiveSheet.ListObjects("Table1").Sort.SortFields.Add _
Key:=Range("Table1[[#All],[Name]]"), SortOn:=xlSortOnValues,
Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveSheet.ListObjects("Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Cheers
Sarah
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Applying Variables to SORT 4
Return to active cell after sort 2
Sort by one column then another. 2
Excel MS Excel 2010 -- Automatically resorted tables when new row is added 2
Undo Macro Action 3
Sort Macro Compatibility 3
Clear Check Box 2
Run sort macro on active worksheet 1

Top