Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you get a macro to reference the worksheet it is being run | Excel Discussion (Misc queries) | |||
Newbie Cell Reference Question... | New Users to Excel | |||
Newbie vba - How do I reference cells in another workbook | Excel Programming | |||
Newbie help with cell reference | Excel Programming | |||
Newbie question on cell reference 2 | Excel Programming |