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.Clear

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

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
___________________

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)



"eleinia" wrote 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("T able1").Sort.SortFields.Clear

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

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

On Jan 18, 1:42*pm, "Jim Cone" wrote:
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)

"eleinia" wrote 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("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

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