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.

I can post some additional VBA if necessary. I'm not sure why I'm running
into this roadblock.

Thanks....Laurie

You may want to post what code you have.

"Alberta Rose" wrote:

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.

I can post some additional VBA if necessary. I'm not sure why I'm running
into this roadblock.

Thanks....Laurie

Here is something else I've tried, but seem to be missing part of the code:


Private Sub mcrBAW_Click()

' mcrBAWMacro
' Macro recorded 8/12/2009 by Authorized User
'
Sheets("Res by Dept w totals").Select
Selection.AutoFilter Field:=2, Criteria1:="Bruce Wallace"

End Sub

I get an error on the Selection.AutoFilter line.

"Alberta Rose" wrote:

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.

I can post some additional VBA if necessary. I'm not sure why I'm running
into this roadblock.

Thanks....Laurie

This is the coding I was using for this issue.

Private Sub mcrAPA_Click()
Application.ScreenUpdating = False

'
' mcrAPA Macro
' Macro recorded 8/17/2009 by LAtchison
'

'
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

The following code is for the next PM. It returns an error on line
..PivotItems("0").Visible = True and if I comment out that line, the next in
line becomes the error.


Private Sub mcrBAW_Click()
Application.ScreenUpdating = False
'
' mcrBAW Macro
' Macro recorded 8/17/2009 by LAtchison
'

'
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

"Alberta Rose" wrote:

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.

I can post some additional VBA if necessary. I'm not sure why I'm running
into this roadblock.

Thanks....Laurie

"Alberta Rose" wrote:

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.

I can post some additional VBA if necessary. I'm not sure why I'm running
into this roadblock.

Thanks....Laurie