R
ryguy7272
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:=xlDatabase, 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).Subtotals = 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).AutoSort
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--
=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:=xlDatabase, 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).Subtotals = 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).AutoSort
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--