Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using variables to reference sheets
I have a series of sheets with names set from variables. I want to reference these sheets later in the macro to do othe functions Eg NewWorksheet.Name = variable1 ....later in the macro TableDestination:=variable1.Range("A10") Will this work?? What is the best way to reference a variable as names? -- marksince198 ----------------------------------------------------------------------- marksince1984's Profile: http://www.excelforum.com/member.php...fo&userid=3348 View this thread: http://www.excelforum.com/showthread.php?threadid=55122 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using variables to reference sheets
Don't set it to the worksheet name, set it to the worksheet object
Set sh = Worksheets("Sheet1") ... TableDestination = sh.Range("A10").Value -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "marksince1984" wrote in message news:marksince1984.29b7yo_1150150204.0161@excelfor um-nospam.com... I have a series of sheets with names set from variables. I want to reference these sheets later in the macro to do other functions Eg NewWorksheet.Name = variable1 ...later in the macro TableDestination:=variable1.Range("A10") Will this work?? What is the best way to reference a variable as names?? -- marksince1984 ------------------------------------------------------------------------ marksince1984's Profile: http://www.excelforum.com/member.php...o&userid=33484 View this thread: http://www.excelforum.com/showthread...hreadid=551223 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using variables to reference sheets
Can you clarify this a bit more for me?? Everytime the macro is run the variable may be different. Ie there could be five sheets with names mon, tues, wed. Tomorrow the could be tues, wed, thurs. I need to be able to call these sheets (b using the variable) Sorry but i am still new to this Bob Phillips Wrote: Don't set it to the worksheet name, set it to the worksheet object Set sh = Worksheets("Sheet1") ... TableDestination = sh.Range("A10").Value -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) -- marksince198 ----------------------------------------------------------------------- marksince1984's Profile: http://www.excelforum.com/member.php...fo&userid=3348 View this thread: http://www.excelforum.com/showthread.php?threadid=55122 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
using variables to reference sheets
Ok so how do you know which you will be accessing?
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "marksince1984" wrote in message news:marksince1984.29baqn_1150153805.9392@excelfor um-nospam.com... Can you clarify this a bit more for me?? Everytime the macro is run the variable may be different. Ie there could be five sheets with names mon, tues, wed. Tomorrow they could be tues, wed, thurs. I need to be able to call these sheets (by using the variable) Sorry but i am still new to this Bob Phillips Wrote: Don't set it to the worksheet name, set it to the worksheet object Set sh = Worksheets("Sheet1") ... TableDestination = sh.Range("A10").Value -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) -- marksince1984 ------------------------------------------------------------------------ marksince1984's Profile: http://www.excelforum.com/member.php...o&userid=33484 View this thread: http://www.excelforum.com/showthread...hreadid=551223 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
using variables to reference sheets
The vba code is only run once everytime the spreadsheet is opened. The user is prompted for a choice (radio buttons), and that choice then dictates the values assigned to the variable. The code then creates sheets and names them after the variable (only if the variables have been changed from their default values, this leaves room for different choices having different numbers of worksheets The code the progresses to place a pivot on each sheet filtered by that variable Example of process: Choices a) "4 leg animals" radio button will set variables 1,2,3... to "Dog","Cat","horse" b) "2 leg animals" radio button will set variables 1,2,3.. to "Human","Bird" c) "3 leg animals" radio button will set variables 1,2,3.. to "Dog with 3 legs" If user chooses a), tabs "Dog","Cat","horse" will be created (I can do this) I then need to create pivot tables on each sheet filtered by the same variable as created the name of that sheet (see code in orgininal text) I would appreciate a good hit around the back of the head to explain what the proper way to use these variables is. Am i getting my idea across?? I understand that what the writer often considers simple is complex to the reader. -- marksince1984 ------------------------------------------------------------------------ marksince1984's Profile: http://www.excelforum.com/member.php...o&userid=33484 View this thread: http://www.excelforum.com/showthread...hreadid=551223 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
using variables to reference sheets
This seems somewhat more complex than the original question, and I am
getting lost as to what you have and what you need. You can easily set a reference to each sheet as you create it, e.g. Set sh1 = Worksheets.Add sh1.Name = "dog" and then use the sh1 object variable later in the code. What exact problem do you have? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "marksince1984" wrote in message news:marksince1984.29d86n_1150243803.3466@excelfor um-nospam.com... The vba code is only run once everytime the spreadsheet is opened. The user is prompted for a choice (radio buttons), and that choice then dictates the values assigned to the variable. The code then creates sheets and names them after the variable (only if the variables have been changed from their default values, this leaves room for different choices having different numbers of worksheets The code the progresses to place a pivot on each sheet filtered by that variable Example of process: Choices a) "4 leg animals" radio button will set variables 1,2,3... to "Dog","Cat","horse" b) "2 leg animals" radio button will set variables 1,2,3.. to "Human","Bird" c) "3 leg animals" radio button will set variables 1,2,3.. to "Dog with 3 legs" If user chooses a), tabs "Dog","Cat","horse" will be created (I can do this) I then need to create pivot tables on each sheet filtered by the same variable as created the name of that sheet (see code in orgininal text) I would appreciate a good hit around the back of the head to explain what the proper way to use these variables is. Am i getting my idea across?? I understand that what the writer often considers simple is complex to the reader. -- marksince1984 ------------------------------------------------------------------------ marksince1984's Profile: http://www.excelforum.com/member.php...o&userid=33484 View this thread: http://www.excelforum.com/showthread...hreadid=551223 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to reference another sheet for two variables | Excel Worksheet Functions | |||
Vary variables in a formula via reference to another cell | Excel Discussion (Misc queries) | |||
reference variables correctly? | Excel Programming | |||
reference variables correctly? | Excel Programming | |||
Using variables to reference columns in VBA | Excel Programming |