Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a Macro to create a Pivot where fields may change
I recently found out that I could host an Excel spreadsheet on the web and
have filters set by each user. The filters are named by the user and then saved on their PC in the hkey_local_machine directory by some vb code so they do nt need admin rights. This way every time the user comes back to the public file they will see their custom filters. I got this to work fine, but I want to pivot the data because it is headcount data that needs to be summarized. There are 11 fields that the count can be filtered by and each field can be displayed or not using another macro. So the field list will be different each time. I need a macro that will pivot whatever data happens to be chosen. I can create a macro that copies the filtered data to another sheet and then pivots all the fields but it hard codes the field names I pick of course. I need it to give variable names to the fields, like field (1), field (2), etc and then pivot them in the order they are listed from left to right. If the user wants to change the order after it is pivoted then that is their perogative. Here is the pivot macro so far" Selection.Copy Sheets("Pivot_source").Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Pivot_source!R1C1:R41C11").CreatePivotTable TableDestination:="", TableName _ :="PivotTable4", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable4").AddFields RowFields:=Array("GROUP", _ "DIVISION", "Program", "STATE", "CITY", "UNION_CODE"), ColumnFields:= _ "PAY_CATEGORY" ActiveSheet.PivotTables("PivotTable4").PivotFields ("Emplid_Cnt").Orientation = _ xlDataField End Sub I need Group etc to be field (1) and so on. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro on filtering pivot table (pivot fields) = debug | Excel Programming | |||
vba code to create pivot table + group fields | Excel Programming | |||
how do i create pivot table from data fields containing forumulas | Excel Worksheet Functions | |||
How do pivot table source fields automatically create tabs | Excel Worksheet Functions | |||
create a macro for a combo box to change a pivot table | Excel Programming |