Dynamic pivot data

[J.E. Sj?berg]

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

[J.E. Sj?berg]

Sorry if I didn't make any sense in my first post.

What I want to do is to fill the data field with data, that datas name is specified in a cell on another sheet. Is that possible?

I have "A15-35", "A36-59" and "M15-35" to choose from when I create a PivotTable manually and I want to make it so that it creates the pivot automatically specified by a cell. The cell will contain "A15-35" or one of the others.

[geekgirlau]

Try this:

[VBA]
Sub test()
Dim strField As String


strField = Sheets("SheetName").Range("MyCell")

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(strField).Orientation = _
xlDataField

With ActiveSheet.PivotTables("pivotKM").PivotFields("Summa av " & strField)
.Calculation = xlPercentOfRow
.NumberFormat = "0%"
End With

ActiveSheet.Name = "Kanalmix"
End Sub
[/VBA]

[J.E. Sj?berg]

Thank you! I got help with this from another source and the solution was the same.

[geekgirlau]

Just a quick note about our procedures:

• If you have posted the same question elsewhere, please post a link to that question. This is known as cross-posting. It can be very frustrating for someone to take the time and effort to assist with a problem, only to find that it has already been solved on another board.
• When you post code, select the code and then click on the "VBA" button. This makes the code a lot easier to read.

[J.E. Sj?berg]

Hi!

I asked a VBA guru in person to help me, so no cross posting!

I will post code with the "VBA" button in the future. Thanks for the help!