using variables to reference sheets

[marksince1984]

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?

 

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[marksince1984]

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]

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" <[email protected]>
wrote in message

 

[marksince1984]

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.

 

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[marksince1984]

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:=xlDatabase, 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").AddDataField 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