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

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

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

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

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

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

using variables to reference sheets
 

outputvar1 has been set previously through the use of radio buttons

this variable could be a different number everytime.

I then need to reference it later (see below in red)

Usually you would use the sheet name here, but as it changes I need to
reference the sheet from a variable.

Does that help??


Code:
--------------------
Set NewSheet1 = Worksheets.Add
NewSheet1.Name = outputvar1

Range("A10").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Control!$A:$X").CreatePivotTable TableDestination:= _
"'[Overheads.Engine.xls]*outputvar1*'!R10C1", TableName:="PivotTable1",
DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("CostCentre")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Account")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("GL Date")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Description")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("RevisedBatch")
.Orientation = xlRowField
.Position = 4
End With
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Certified Amount"), "Count of Certified Amount", _
xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("E16").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Count of Certified Amount") _
.Function = xlSum
Range("D11").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields ("RevisedBatch").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
Range("C11").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Description").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields ("CostCentre").CurrentPage = _
*outputvar1*
Range("B11").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields ("GL Date").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False)
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("GL Date")
.PivotItems("(blank)").Visible = False
End With
Range("A11").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Account")
.LayoutBlankLine = True
.LayoutForm = xlOutline
End With
ActiveWindow.SmallScroll Down:=0

--------------------


--
marksince1984
------------------------------------------------------------------------
marksince1984's Profile: http://www.excelforum.com/member.php...o&userid=33484
View this thread: http://www.excelforum.com/showthread...hreadid=551223