Slight VBA hickup

[zerokreap]

I want the following code to refresh all my pivots (on protecte
sheets), then activate cell B2 on sheet "Source." The code does what
want when I run it in the VBA editor, but when I use the in-sheet butto
with the assigned macro, it never ends on the "Source" sheet. Am
missing a command after the final for loop. Any thoughts?

Private Sub FixSource()
'
' FixSource Macro
'
Dim ws As Worksheet
Dim pt As PivotTable
Dim pc As PivotCache

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="password"
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next ws
For Each pc In ActiveWorkbook.PivotCaches
On Error Resume Next
pc.Refresh
Next pc

For Each ws In ActiveWorkbook.Worksheets
ws.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
Password:="password"
Next ws

Worksheets("Source").Select
Range("B2").Select


End Su

 

[GS]

Try this...

Private Sub FixSource2()

Dim ws As Worksheet, As PivotTable, As PivotCache

' On Error Resume Next
With ActiveWorkbook
For Each ws In .Worksheets
ws.Unprotect Password:="password"
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next 'ws

For Each pc In .PivotCaches: pc.Refresh: Next 'pc

For Each ws In .Worksheets
ws.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, Password:="password"
Next 'ws
End With 'ActiveWorkbook

Application.Goto Sheets("Source").Range("B2")
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

missed something in copy/paste...

Private Sub FixSource2()

Dim ws As Worksheet, pt As PivotTable, pc As PivotCache

' On Error Resume Next
With ActiveWorkbook
For Each ws In .Worksheets
ws.Unprotect Password:="password"
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next 'ws

For Each pc In .PivotCaches: pc.Refresh: Next 'pc

For Each ws In .Worksheets
ws.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, Password:="password"
Next 'ws
End With 'ActiveWorkbook

Application.Goto Sheets("Source").Range("B2")
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[zerokreap]

This got set aside for the past week. Thanks so much for your reply.
will give it a try and let you know how ti works.

Thanks Again!

;1609993']missed something in copy/paste...
-

Private Sub FixSource2()
-

Dim ws As Worksheet, pt As PivotTable, pc As PivotCache-

' On Error Resume Next
With ActiveWorkbook
For Each ws In .Worksheets
ws.Unprotect Password:="password"
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next 'ws

For Each pc In .PivotCaches: pc.Refresh: Next 'pc

For Each ws In .Worksheets
ws.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, Password:="password"
Next 'ws
End With 'ActiveWorkbook

Application.Goto Sheets("Source").Range("B2")
End Sub-

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussio