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