Macro for PivotTable Field List Options

[sg123]

Dear Reader

My excel workbook consists of the following:-

Sheet1: Data for pivot table
Sheet2: Pivot Table
Sheet3: Options to be checked in the "Values" of the PivotTable Field List.

Is there a macro for automatically checking options in the pivottable field list from the values in Sheet3?


Thanks in advance. :)

[Rabbit]

Your question has been moved to the Access VBA forum as that's closer to the topic of the thread.

Yes there is. If you record a macro of you checking the option for the pivot table, you can then go in and edit the literal reference to your cell reference.

[sg123]

Dear Rabbit

Thank you for your post. I was able to record the following code:-

 Dim UserName As String
Sheets("Sheet3").Activate
 Range("A2").Select
 
 Do Until (ActiveCell.Value = "")
 UserName = ActiveCell.Value
 
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields(UserName), "Count of " & UserName, xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of " & UserName).Caption = "Sum of " & UserName
    .Function = xlSum
End With
Loop
End Sub

But, I am getting run-time error '1004' : Unable to get the PivotTables property of the Working Class. Any ideas?

[Rabbit]

Your active sheet is sheet3. I thought you said your pivot table was on sheet2? You would need to use sheet2, not your active sheet.

[sg123]

Now I am getting the error 'Invalid Qualifier' for the 'Sum of & UserName' in the 4th last line in the code which reads as follows:-

Dim UserName As String
 
 Sheets("Sheet3").Activate
 Range("A2").Select
 
 Do Until (ActiveCell.Value = "")
 UserName = ActiveCell.Value
  Sheets("Sheet2").Activate
 
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields(UserName), "Count of " & UserName, xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of " & UserName).Caption = "Sum of" & UserName.Function = xlSum
    End With
Loop
End Sub
 

[Rabbit]

Compare line 11 in your second block of code with lines 9 and 10 in your first block of codes.

Also, you have an infinite loop. I don't understand the loop you initiate on line 6. You never change its value so the loop is never going to exit.

Finally, I would avoid using the code to activate sheets. Just reference the sheets explicitly.

[sg123]

Thank you for your help. My problem is solved. :)

[Rabbit]

Glad you got it working.

I moved the thread again because we just opened up a new Excel forum.