J.E. Sj?berg
04-23-2007, 08:25 AM
Hi!
First time posting and quite new to Excel, pivot tables and VBA.
I'm trying to create pivot tables defined by cells in another sheet.
I always want to have the same columns and rows in the pivot table, but the data can vary. Because there are alot of these tables being created I want to make it automatic, so my thought is to write the name of the column of data i want to use in a cell on a "Detail" sheet and then get that text when specifying what data to use when creating the pivot. Does that make any sense?
I have one sheet called Data and one called Detalj. I define the name 'pivotData' as a dynamic range for what is found in Data!.
The data I want is "A15-34" and that's not the only age range there is in the data. As it is now I must code the wanted data into the code for each pivot I want to create. My wish is to write A15-34 in cell A2 in the Detalj sheet and then use that for the creation of the pivot.
Sorry if this is a bit of a newbie question but I'd really appreciate the help.
Here's the code right now (I just made a makro and edited it after to make the data selection dynamic):
Sub test()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"pivotData").CreatePivotTable TableDestination:="", TableName:= _
"pivotKM", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("pivotKM").AddFields RowFields:="Produkt", _
ColumnFields:="Kanal"
ActiveSheet.PivotTables("pivotKM").PivotFields("A15-34").Orientation = _
xlDataField
With ActiveSheet.PivotTables("pivotKM").PivotFields("Summa av A15-34")
.Calculation = xlPercentOfRow
.NumberFormat = "0%"
End With
ActiveSheet.Name = "Kanalmix"
End Sub
First time posting and quite new to Excel, pivot tables and VBA.
I'm trying to create pivot tables defined by cells in another sheet.
I always want to have the same columns and rows in the pivot table, but the data can vary. Because there are alot of these tables being created I want to make it automatic, so my thought is to write the name of the column of data i want to use in a cell on a "Detail" sheet and then get that text when specifying what data to use when creating the pivot. Does that make any sense?
I have one sheet called Data and one called Detalj. I define the name 'pivotData' as a dynamic range for what is found in Data!.
The data I want is "A15-34" and that's not the only age range there is in the data. As it is now I must code the wanted data into the code for each pivot I want to create. My wish is to write A15-34 in cell A2 in the Detalj sheet and then use that for the creation of the pivot.
Sorry if this is a bit of a newbie question but I'd really appreciate the help.
Here's the code right now (I just made a makro and edited it after to make the data selection dynamic):
Sub test()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"pivotData").CreatePivotTable TableDestination:="", TableName:= _
"pivotKM", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("pivotKM").AddFields RowFields:="Produkt", _
ColumnFields:="Kanal"
ActiveSheet.PivotTables("pivotKM").PivotFields("A15-34").Orientation = _
xlDataField
With ActiveSheet.PivotTables("pivotKM").PivotFields("Summa av A15-34")
.Calculation = xlPercentOfRow
.NumberFormat = "0%"
End With
ActiveSheet.Name = "Kanalmix"
End Sub