VBA, Macros and Pivot Tables

A

Alberta Rose

I have a contract spreadsheet that I needed to make more user friendly for
the Project Managers.

In this spreadsheet there are multiple workbooks, the main ones I am using
for this application are "Database" and "Res by Dept w Totals". I have added
a new worksheet at the beginning of the others called "Main". On this "Main"
worksheet, I have put buttons with each PM's name on and made macros that
will go into the "Database tab" and through the filter only pull up the
contract information that pertains to that name. On the "Database" tab I
have also put a button with macro to return to the "Main" worksheet.

The "Res by Dept w totals" tab is also a filtered list but in a report
format/view. I used the same procedure and built macros that reside in
buttons on "Main" that will pull up only that particular Project Manager's
information when selected by turning all the .PivotItems("name").Visible =
False - except for the name I am selecting.

My problem is, I need a macro that will reset the "Res by Dept w totals" to
include "all" so that the next Project Manager's report can be requested. I
have tried recording a macro to do that - to but I get an error when I try to
run the next PM's reports - VBA isn't liking the coding that resets all the
..visible=true parameters. This is the coding that is there now:
..PivotItems("Adam Mohr").Visible = True.

Here is some of the coding:

Sub mcrAPA()
'
' mcrAPA Macro
' Macro recorded 8/17/2009 by Authorized User
'
Application.ScreenUpdating = False

'
Sheets("Res by Dept w totals").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Primary Resource Full Name")
.PivotItems("0").Visible = False
.PivotItems("Adam Mohr").Visible = False
.PivotItems("Aimee Johnston").Visible = False
.PivotItems("Alanna Tremblay").Visible = False
.PivotItems("Baldwin Preila").Visible = False
.PivotItems("Blair Neal").Visible = False
.PivotItems("Brad Pietrzak").Visible = False
.PivotItems("Bruce Wallace").Visible = False
.PivotItems("Carrie Quaschnick").Visible = False
.PivotItems("Christopher Jack").Visible = False
.PivotItems("Clay Fuller").Visible = False
.PivotItems("Colin Thibodeau").Visible = False
.PivotItems("Colin Ukrainec").Visible = False
.PivotItems("Darrell Donecz").Visible = False
.PivotItems("Dave Dowie").Visible = False
.PivotItems("Dave Thompson").Visible = False
.PivotItems("Denis Lepage").Visible = False
.PivotItems("Derek Bristol").Visible = False
.PivotItems("Devin Sandor").Visible = False
.PivotItems("Dilip Bodarya").Visible = False
.PivotItems("Don Morris").Visible = False
.PivotItems("Ed Jorgensen").Visible = False
.PivotItems("Freddie Chacin").Visible = False
.PivotItems("George Kellendonk").Visible = False
.PivotItems("Ghulam Murtaza").Visible = False
.PivotItems("Greg Guse").Visible = False
.PivotItems("Jason Vest").Visible = False
.PivotItems("Jennifer Stuart").Visible = False
.PivotItems("Jerry Hutchings").Visible = False
.PivotItems("Joe Sloan").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Primary Resource Full Name")
.PivotItems("Jonathan Wong").Visible = False
.PivotItems("Juan Daza").Visible = False
.PivotItems("Kathy Vanderveen").Visible = False
.PivotItems("Ken Barnes").Visible = False
.PivotItems("Laurie Atchison").Visible = False
.PivotItems("Local Hire").Visible = False
.PivotItems("Marc Pelletier").Visible = False
.PivotItems("Mark Fraser").Visible = False
.PivotItems("Marti Brockman").Visible = False
.PivotItems("Mike Belger").Visible = False
.PivotItems("Mike Dautovich").Visible = False
.PivotItems("Mitul Adhiya").Visible = False
.PivotItems("Neal Saquiliyan").Visible = False
.PivotItems("Nick Gerke").Visible = False
.PivotItems("Prakash Patel").Visible = False
.PivotItems("Rakesh Padalia").Visible = False
.PivotItems("Remi Chenard").Visible = False
.PivotItems("Rob Anderson").Visible = False
.PivotItems("Shams Rehman").Visible = False
.PivotItems("Steve Brown").Visible = False
.PivotItems("TBD").Visible = False
.PivotItems("TBD1 - 2009 Plan").Visible = False
.PivotItems("TBD2 - 2009 Backup Opp's").Visible = False
.PivotItems("TBD3 - Other (Long Term)").Visible = False
.PivotItems("TBD4 - 2010 Plan").Visible = False
.PivotItems("Tom Kodron").Visible = False
.PivotItems("Trevor Kozak").Visible = False
.PivotItems("Victor Kovalenko").Visible = False
.PivotItems("Viren Vyas").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Primary Resource Full Name")
.PivotItems("Vladislav Tolmachev").Visible = False
.PivotItems("Werner Von Elling").Visible = False
.PivotItems("#N/A").Visible = False
.PivotItems("(blank)").Visible = False
End With
End Sub
Sub mcrBAW()
'
' mcrBAW Macro
' Macro recorded 8/17/2009 by Authorized User
'

'
Sheets("Res by Dept w totals").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Primary Resource Full Name")
.PivotItems("0").Visible = True
.PivotItems("Adam Mohr").Visible = True
.PivotItems("Aimee Johnston").Visible = True
.PivotItems("Alanna Tremblay").Visible = True
.PivotItems("Baldwin Preila").Visible = True
.PivotItems("Blair Neal").Visible = True
.PivotItems("Brad Pietrzak").Visible = True
.PivotItems("Bruce Wallace").Visible = True
.PivotItems("Carrie Quaschnick").Visible = True
.PivotItems("Christopher Jack").Visible = True
.PivotItems("Clay Fuller").Visible = True
.PivotItems("Colin Thibodeau").Visible = True
.PivotItems("Colin Ukrainec").Visible = True
.PivotItems("Darrell Donecz").Visible = True
.PivotItems("Dave Dowie").Visible = True
.PivotItems("Dave Thompson").Visible = True
.PivotItems("Denis Lepage").Visible = True
.PivotItems("Derek Bristol").Visible = True
.PivotItems("Devin Sandor").Visible = True
.PivotItems("Dilip Bodarya").Visible = True
.PivotItems("Don Morris").Visible = True
.PivotItems("Ed Jorgensen").Visible = True
.PivotItems("Freddie Chacin").Visible = True
.PivotItems("George Kellendonk").Visible = True
.PivotItems("Ghulam Murtaza").Visible = True
.PivotItems("Greg Guse").Visible = True
.PivotItems("Jason Vest").Visible = True
.PivotItems("Jennifer Stuart").Visible = True
.PivotItems("Jerry Hutchings").Visible = True
.PivotItems("Joe Sloan").Visible = True
.PivotItems("Jonathan Wong").Visible = True
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Primary Resource Full Name")
.PivotItems("Juan Daza").Visible = True
.PivotItems("Kathy Vanderveen").Visible = True
.PivotItems("Ken Barnes").Visible = True
.PivotItems("Laurie Atchison").Visible = True
.PivotItems("Local Hire").Visible = True
.PivotItems("Marc Pelletier").Visible = True
.PivotItems("Mark Fraser").Visible = True
.PivotItems("Marti Brockman").Visible = True
.PivotItems("Mike Belger").Visible = True
.PivotItems("Mike Dautovich").Visible = True
.PivotItems("Mitul Adhiya").Visible = True
.PivotItems("Neal Saquiliyan").Visible = True
.PivotItems("Nick Gerke").Visible = True
.PivotItems("Prakash Patel").Visible = True
.PivotItems("Rakesh Padalia").Visible = True
.PivotItems("Remi Chenard").Visible = True
.PivotItems("Rob Anderson").Visible = True
.PivotItems("Shams Rehman").Visible = True
.PivotItems("Steve Brown").Visible = True
.PivotItems("TBD").Visible = True
.PivotItems("TBD1 - 2009 Plan").Visible = True
.PivotItems("TBD2 - 2009 Backup Opp's").Visible = True
.PivotItems("TBD3 - Other (Long Term)").Visible = True
.PivotItems("TBD4 - 2010 Plan").Visible = True
.PivotItems("Tom Kodron").Visible = True
.PivotItems("Trevor Kozak").Visible = True
.PivotItems("Victor Kovalenko").Visible = True
.PivotItems("Viren Vyas").Visible = True
.PivotItems("Vladislav Tolmachev").Visible = True
.PivotItems("Werner Von Elling").Visible = True
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Primary Resource Full Name")
.PivotItems("#N/A").Visible = True
.PivotItems("(blank)").Visible = True
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Primary Resource Full Name")
.PivotItems("0").Visible = False
.PivotItems("Adam Mohr").Visible = False
.PivotItems("Aimee Johnston").Visible = False
.PivotItems("Al Acosta").Visible = False
.PivotItems("Alanna Tremblay").Visible = False
.PivotItems("Baldwin Preila").Visible = False
.PivotItems("Blair Neal").Visible = False
.PivotItems("Brad Pietrzak").Visible = False
.PivotItems("Carrie Quaschnick").Visible = False
.PivotItems("Christopher Jack").Visible = False
.PivotItems("Clay Fuller").Visible = False
.PivotItems("Colin Thibodeau").Visible = False
.PivotItems("Colin Ukrainec").Visible = False
.PivotItems("Darrell Donecz").Visible = False
.PivotItems("Dave Dowie").Visible = False
.PivotItems("Dave Thompson").Visible = False
.PivotItems("Denis Lepage").Visible = False
.PivotItems("Derek Bristol").Visible = False
.PivotItems("Devin Sandor").Visible = False
.PivotItems("Dilip Bodarya").Visible = False
.PivotItems("Don Morris").Visible = False
.PivotItems("Ed Jorgensen").Visible = False
.PivotItems("Freddie Chacin").Visible = False
.PivotItems("George Kellendonk").Visible = False
.PivotItems("Ghulam Murtaza").Visible = False
.PivotItems("Greg Guse").Visible = False
.PivotItems("Jason Vest").Visible = False
.PivotItems("Jennifer Stuart").Visible = False
.PivotItems("Jerry Hutchings").Visible = False
.PivotItems("Joe Sloan").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Primary Resource Full Name")
.PivotItems("Jonathan Wong").Visible = False
.PivotItems("Juan Daza").Visible = False
.PivotItems("Kathy Vanderveen").Visible = False
.PivotItems("Ken Barnes").Visible = False
.PivotItems("Laurie Atchison").Visible = False
.PivotItems("Local Hire").Visible = False
.PivotItems("Marc Pelletier").Visible = False
.PivotItems("Mark Fraser").Visible = False
.PivotItems("Marti Brockman").Visible = False
.PivotItems("Mike Belger").Visible = False
.PivotItems("Mike Dautovich").Visible = False
.PivotItems("Mitul Adhiya").Visible = False
.PivotItems("Neal Saquiliyan").Visible = False
.PivotItems("Nick Gerke").Visible = False
.PivotItems("Prakash Patel").Visible = False
.PivotItems("Rakesh Padalia").Visible = False
.PivotItems("Remi Chenard").Visible = False
.PivotItems("Rob Anderson").Visible = False
.PivotItems("Shams Rehman").Visible = False
.PivotItems("Steve Brown").Visible = False
.PivotItems("TBD").Visible = False
.PivotItems("TBD1 - 2009 Plan").Visible = False
.PivotItems("TBD2 - 2009 Backup Opp's").Visible = False
.PivotItems("TBD3 - Other (Long Term)").Visible = False
.PivotItems("TBD4 - 2010 Plan").Visible = False
.PivotItems("Tom Kodron").Visible = False
.PivotItems("Trevor Kozak").Visible = False
.PivotItems("Victor Kovalenko").Visible = False
.PivotItems("Viren Vyas").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Primary Resource Full Name")
.PivotItems("Vladislav Tolmachev").Visible = False
.PivotItems("Werner Von Elling").Visible = False
.PivotItems("#N/A").Visible = False
.PivotItems("(blank)").Visible = False
End With
End Sub
 
R

ryguy7272

That's a lot of code! I did somethign similar for a client once. I used a
ListBox, and this was the Input Range:
R8C1:R59C1

This was the Cell Link:
R7C2

This is the VBA:

Sub PivotTableInputs()


Sheets("MergeSheet").Select

Cells.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Sheets("MergeSheet").Range("A1").CurrentRegion).CreatePivotTable _
TableDestination:="", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select


With
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Summary").Range("C5").Value)
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(Sheets("Summary").Range("C5").Value),
"Count of ", xlCount

With
ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Summary").Range("C5").Value)
.Orientation = xlRowField
.Position = 1
End With


ActiveSheet.PivotTables("PivotTable1").PivotFields(Sheets("Summary").Range("C5").Value).AutoSort _
xlDescending, "Count of "

Charts.Add
With ActiveChart.ChartGroups(1)
.Overlap = 100
.GapWidth = 0
.HasSeriesLines = False
.VaryByCategories = False
End With
End Sub

I was building a PivotTable on the fly and linking that to a chart. Does
that help?

Ryan---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dynamic Pivot Item Selection 1
Pivot Table - How do I "Show All Data" of a particular field? 6
Macro on filtering pivot table (pivot fields) => debug 2
VBA Code For Pivot Table. 1
Creating reports per Pivot Table PivotItem 1
All Pivot table include different pivot field to save as individualworkbook 5
automatic pivot tables 1
VBA Pivot Table Question 1

Top