Pivot Table, Variable Inputs
 

I have inputs form a ListBox, and a simple index function:
=INDEX($A$8:$A$59,$B$7)
to get the items in the box to display in a cell as names instead of
numbers. The names go into cell C5, which I named VarInput. I thought I
could reference this cell for a Pivot Table, and it seems to almost work, but
it doesn't quite work. Below is the code. Can someone please tell me what
is wrong?

I'm pretty sure I need code like this to reference the specific cell:
..PivotFields(Sheets("Summary").Range("VarInput"). Value)

The code fails in two places, indicated below (I know about the second one
because I commented out the first one and reran the code and got a second
error).

Sub PivotTableInputs()

Sheets("MergeSheet").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"MergeSheet!R1C1:R375C24").CreatePivotTable TableDestination:="",
TableName _
:="PivotTable3", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

ActiveSheet.PivotTables("PivotTable3").PivotFields (Sheets("Summary").Range("VarInput").Value).Subtot als = Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
ActiveSheet.PivotTables("PivotTable3").AddFields
RowFields:=Range("VarInput")

ActiveSheet.PivotTables("PivotTable3").PivotFields (Sheets("Summary").Range("VarInput").Value) = xlDataField
ActiveWorkbook.ShowPivotTableFieldList = True

'first problem

ActiveSheet.PivotTables("PivotTable3").PivotFields (Sheets("Summary").Range("VarInput").Value).AutoSo rt
xlDescending , Range("VarInput")

'second problem
With
ActiveSheet.PivotTables("PivotTable3").PivotFields (Sheets("Summary").Range("VarInput").Value)
.PivotItems("(blank)").Visible = False
End With

Charts.Add
With ActiveChart.ChartGroups(1)
.Overlap = 100
.GapWidth = 0
.HasSeriesLines = False
.VaryByCategories = False
End With
End Sub

Can someone please tell me what is wrong?


Regards,
Ryan--


--
RyGuy

Pivot Table, Variable Inputs
 

Resolved!!
http://www.microsoft.com/office/comm...sloc=en-us&p=1

PS, thanks for the help Ivan!!

Regards,
Ryan---

--
RyGuy


"ryguy7272" wrote:

I have inputs form a ListBox, and a simple index function:
=INDEX($A$8:$A$59,$B$7)
to get the items in the box to display in a cell as names instead of
numbers. The names go into cell C5, which I named VarInput. I thought I
could reference this cell for a Pivot Table, and it seems to almost work, but
it doesn't quite work. Below is the code. Can someone please tell me what
is wrong?

I'm pretty sure I need code like this to reference the specific cell:
.PivotFields(Sheets("Summary").Range("VarInput").V alue)

The code fails in two places, indicated below (I know about the second one
because I commented out the first one and reran the code and got a second
error).

Sub PivotTableInputs()

Sheets("MergeSheet").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"MergeSheet!R1C1:R375C24").CreatePivotTable TableDestination:="",
TableName _
:="PivotTable3", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

ActiveSheet.PivotTables("PivotTable3").PivotFields (Sheets("Summary").Range("VarInput").Value).Subtot als = Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
ActiveSheet.PivotTables("PivotTable3").AddFields
RowFields:=Range("VarInput")

ActiveSheet.PivotTables("PivotTable3").PivotFields (Sheets("Summary").Range("VarInput").Value) = xlDataField
ActiveWorkbook.ShowPivotTableFieldList = True

'first problem

ActiveSheet.PivotTables("PivotTable3").PivotFields (Sheets("Summary").Range("VarInput").Value).AutoSo rt
xlDescending , Range("VarInput")

'second problem
With
ActiveSheet.PivotTables("PivotTable3").PivotFields (Sheets("Summary").Range("VarInput").Value)
.PivotItems("(blank)").Visible = False
End With

Charts.Add
With ActiveChart.ChartGroups(1)
.Overlap = 100
.GapWidth = 0
.HasSeriesLines = False
.VaryByCategories = False
End With
End Sub

Can someone please tell me what is wrong?


Regards,
Ryan--


--
RyGuy