Newbie, I dont want macro to reference worksheet name
 

2007 Excel, I am trying to create macros by recording my actions but when I
do they dont work cause the name of the worksheet is different. Tried the
relative reference but doesnt help. Tried deleting the reference out of macro
but doesnt work. Just want generic macro that will work on any file name and
any worksheet name. Ex. even to sort when you select the page in upper left.

Newbie, I dont want macro to reference worksheet name
 

ActiveWorkbook
ActiveSheet
ActiveCell

Make the required edits to get rid of hard-coded references.


Gord Dibben MS Excel MVP


On Thu, 29 Mar 2007 12:24:02 -0700, Michael
wrote:

2007 Excel, I am trying to create macros by recording my actions but when I
do they dont work cause the name of the worksheet is different. Tried the
relative reference but doesnt help. Tried deleting the reference out of macro
but doesnt work. Just want generic macro that will work on any file name and
any worksheet name. Ex. even to sort when you select the page in upper left.

Newbie, I dont want macro to reference worksheet name
 

THANKN YOU for your help.

I tried to do as you said but I get error cause I dont know the exact way to
do it.
here is an ex. that it makes.

ActiveWorkbook.Worksheets("EventQuery-2007-03-29-14-59-24(").Sort.SortFields.
_
Clear

ActiveWorkbook.Worksheets("EventQuery-2007-03-29-14-59-24(").Sort.SortFields.
_
Add Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("EventQuery-2007-03-29-14-59-24(").Sort


"Gord Dibben" wrote:

ActiveWorkbook
ActiveSheet
ActiveCell

Make the required edits to get rid of hard-coded references.


Gord Dibben MS Excel MVP


On Thu, 29 Mar 2007 12:24:02 -0700, Michael
wrote:

2007 Excel, I am trying to create macros by recording my actions but when I
do they dont work cause the name of the worksheet is different. Tried the
relative reference but doesnt help. Tried deleting the reference out of macro
but doesnt work. Just want generic macro that will work on any file name and
any worksheet name. Ex. even to sort when you select the page in upper left.

Newbie, I dont want macro to reference worksheet name
 

Can't test it on this machine, but you can try

ActiveSheet.Sort.SortFields.Clear

ActiveSheet.Sort.SortFields.Add _
Key:=Range("B1"), _
SortOn:=xlSortOnValues, -
Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveSheet.Sort

In article ,
Michael wrote:

THANKN YOU for your help.

I tried to do as you said but I get error cause I dont know the exact way to
do it.
here is an ex. that it makes.

ActiveWorkbook.Worksheets("EventQuery-2007-03-29-14-59-24(").Sort.SortFields.
_
Clear

ActiveWorkbook.Worksheets("EventQuery-2007-03-29-14-59-24(").Sort.SortFields.
_
Add Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("EventQuery-2007-03-29-14-59-24(").Sort


"Gord Dibben" wrote:

ActiveWorkbook
ActiveSheet
ActiveCell

Make the required edits to get rid of hard-coded references.


Gord Dibben MS Excel MVP


On Thu, 29 Mar 2007 12:24:02 -0700, Michael

wrote:

2007 Excel, I am trying to create macros by recording my actions but when
I
do they dont work cause the name of the worksheet is different. Tried the
relative reference but doesnt help. Tried deleting the reference out of
macro
but doesnt work. Just want generic macro that will work on any file name
and
any worksheet name. Ex. even to sort when you select the page in upper
left.

Newbie, I dont want macro to reference worksheet name
 

It means you can not use special name of :
Cell, Sheet, Workbook but you have to set a variable for them in order to
use them unabsolute, just like:

Set Wk = ActiveSheet or Set Wk = Sheets("sheetname") etc...

--

Regards,

Halim


"Gord Dibben" wrote:

ActiveWorkbook
ActiveSheet
ActiveCell

Make the required edits to get rid of hard-coded references.


Gord Dibben MS Excel MVP


On Thu, 29 Mar 2007 12:24:02 -0700, Michael

wrote:

2007 Excel, I am trying to create macros by recording my actions but when
I
do they dont work cause the name of the worksheet is different. Tried the
relative reference but doesnt help. Tried deleting the reference out of
macro
but doesnt work. Just want generic macro that will work on any file name
and
any worksheet name. Ex. even to sort when you select the page in upper
left.

Newbie, I dont want macro to reference worksheet name
 

I tired all the suggestions and still cant get it to work.
I tried the below but cant figure out the right way to use the variable of
Wk with out error.
I know its probably me being obtuse but can someone give me an example based
on this (the excel help stinks)


ActiveWorkbook.Worksheets("EventQuery-2007-03-29-14-59-24(").Sort.SortFields.
_
Add Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
THANKS

"Halim" wrote:

It means you can not use special name of :
Cell, Sheet, Workbook but you have to set a variable for them in order to
use them unabsolute, just like:

Set Wk = ActiveSheet or Set Wk = Sheets("sheetname") etc...

--

Regards,

Halim


"Gord Dibben" wrote:

ActiveWorkbook
ActiveSheet
ActiveCell

Make the required edits to get rid of hard-coded references.


Gord Dibben MS Excel MVP


On Thu, 29 Mar 2007 12:24:02 -0700, Michael

wrote:

2007 Excel, I am trying to create macros by recording my actions but when
I
do they dont work cause the name of the worksheet is different. Tried the
relative reference but doesnt help. Tried deleting the reference out of
macro
but doesnt work. Just want generic macro that will work on any file name
and
any worksheet name. Ex. even to sort when you select the page in upper
left.

Newbie, I dont want macro to reference worksheet name
 

If you want your code to be independent of worksheet names, then you
need to stop putting fixed worksheet names in your code!

Instead let the variable stand for the worksheet to be operated on.

In this case,

Dim Wk As Worksheet
Set Wk = ActiveSheet
Wk.Sort.SortFields.Add _
Key:=Range("B1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal


In article ,
Michael wrote:

I tired all the suggestions and still cant get it to work.
I tried the below but cant figure out the right way to use the variable of
Wk with out error.
I know its probably me being obtuse but can someone give me an example based
on this (the excel help stinks)


ActiveWorkbook.Worksheets("EventQuery-2007-03-29-14-59-24(").Sort.SortFields.
_
Add Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
THANKS

"Halim" wrote:

It means you can not use special name of :
Cell, Sheet, Workbook but you have to set a variable for them in order to
use them unabsolute, just like:

Set Wk = ActiveSheet or Set Wk = Sheets("sheetname") etc...

Newbie, I dont want macro to reference worksheet name
 

Better:

Dim Wk As Worksheet
Set Wk = ActiveSheet
Wk.Sort.SortFields.Add _
Key:=Wk.Range("B1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal


In article ,
JE McGimpsey wrote:

In this case,

Dim Wk As Worksheet
Set Wk = ActiveSheet
Wk.Sort.SortFields.Add _
Key:=Range("B1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal

Newbie, I dont want macro to reference worksheet name
 

SWEET!
Told you I was a newbie, I was actually not writing this, but trying to use
the record macro.
Now I can record and edit to get it to work.
Seems like a dumb way for MS to do it by default.
THANKS

"JE McGimpsey" wrote:

If you want your code to be independent of worksheet names, then you
need to stop putting fixed worksheet names in your code!

Instead let the variable stand for the worksheet to be operated on.

In this case,

Dim Wk As Worksheet
Set Wk = ActiveSheet
Wk.Sort.SortFields.Add _
Key:=Range("B1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal


In article ,
Michael wrote:

I tired all the suggestions and still cant get it to work.
I tried the below but cant figure out the right way to use the variable of
Wk with out error.
I know its probably me being obtuse but can someone give me an example based
on this (the excel help stinks)


ActiveWorkbook.Worksheets("EventQuery-2007-03-29-14-59-24(").Sort.SortFields.
_
Add Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
THANKS

"Halim" wrote:

It means you can not use special name of :
Cell, Sheet, Workbook but you have to set a variable for them in order to
use them unabsolute, just like:

Set Wk = ActiveSheet or Set Wk = Sheets("sheetname") etc...

Newbie, I dont want macro to reference worksheet name
 

Great,
One last question.
How do I keep doing that with this example too.
Tried to use the same concept but it doesnt like it here.
Again thanks so much for you time
Sub Pivot()
'
' Pivot Macro
'

'
Dim wk As Worksheet
Set wk = ActiveSheet
Cells.Select
Range("B1").Activate
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"wk!R1C1:R1048576C14", Version:= _
xlPivotTableVersion12).CreatePivotTable
TableDestination:="Sheet1!R3C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Category")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Src Host")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Src Host"), "Count of Src Host", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Dest Host")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Src Host").AutoSort _
xlDescending, "Count of Src Host"
Range("A5").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Dest Host").AutoSort _
xlAscending, "Dest Host"
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Src
Host").ShowDetail = _
False
End Sub


"JE McGimpsey" wrote:

Better:

Dim Wk As Worksheet
Set Wk = ActiveSheet
Wk.Sort.SortFields.Add _
Key:=Wk.Range("B1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal


In article ,
JE McGimpsey wrote:

In this case,

Dim Wk As Worksheet
Set Wk = ActiveSheet
Wk.Sort.SortFields.Add _
Key:=Range("B1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal

Newbie, I dont want macro to reference worksheet name
 

Great!
One last question, I tried to carry on this concept further but I fail at
the wk in this example
Again thanks so much for you time!

Sub Pivot()
'
' Pivot Macro
'

'
Dim wk As Worksheet
Set wk = ActiveSheet
Cells.Select
Range("B1").Activate
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"wk!R1C1:R1048576C14", Version:= _
xlPivotTableVersion12).CreatePivotTable
TableDestination:="Sheet1!R3C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Category")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Src Host")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Src Host"), "Count of Src Host", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Dest Host")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Src Host").AutoSort _
xlDescending, "Count of Src Host"
Range("A5").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Dest Host").AutoSort _
xlAscending, "Dest Host"
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Src
Host").ShowDetail = _
False
End Sub


"JE McGimpsey" wrote:

Better:

Dim Wk As Worksheet
Set Wk = ActiveSheet
Wk.Sort.SortFields.Add _
Key:=Wk.Range("B1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal


In article ,
JE McGimpsey wrote:

In this case,

Dim Wk As Worksheet
Set Wk = ActiveSheet
Wk.Sort.SortFields.Add _
Key:=Range("B1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal