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.