Include another worksheet in a Private Sub.

[Livin404]

Hello I have a code which works great. However, because my primary pivot table is unlocked and re-locked with the following code it doesn't extend to another worksheet where the final results will reside. The other worksheet is "Data Records" I would like this to apply to. It is on a private sub due to this being on a User Form which where the Cmd button is.

Thank you,

Private Sub CmdRefresh_click()
Call Reset
Dim xpt As PivotTable
    With ActiveSheet
        .Unprotect Password:="mozzer"
        For Each xpt In .PivotTables
            xpt.RefreshTable
        Next xpt
    .Protect Password:="mozzer", _
        AllowUsingPivotTables:=True
            End With
End Sub

 

[breynolds0431]

Would replacing With Activesheet with With ThisWorkBook.Sheets("Data Records") work?

 

[Livin404]

I’m out of town now, but I’ll check tomorrow evening. I will say Will your version include the current worksheet in addition to the “data Records”. I need both of them to allowed to be refreshed. The current code is good I just need to add the worksheet “Data records”.

Thank you so much.

 

[breynolds0431]

I’m out of town now, but I’ll check tomorrow evening. I will say Will your version include the current worksheet in addition to the “data Records”. I need both of them to allowed to be refreshed. The current code is good I just need to add the worksheet “Data records”.

Thank you so much.

Oh, that makes sense. Try the below using an array of the two sheets. Not sure what the other sheet's name is, so replace "data1" with the actual sheet name.

Dim shts, i as long
shts = Array("data1", "Data Records") 'Revise data1 to actual name

For i = LBound(shts) To UBound(shts)
    Call Reset
    Dim xpt As PivotTable
    With ActiveSheet
        .Unprotect Password:="mozzer"
        For Each xpt In .PivotTables
            xpt.RefreshTable
        Next xpt
        .Protect Password:="mozzer", _
        AllowUsingPivotTables:=True
    End With
Next

 

[Livin404]

Oh, that makes sense. Try the below using an array of the two sheets. Not sure what the other sheet's name is, so replace "data1" with the actual sheet name.

Dim shts, i as long
shts = Array("data1", "Data Records") 'Revise data1 to actual name

For i = LBound(shts) To UBound(shts)
    Call Reset
    Dim xpt As PivotTable
    With ActiveSheet
        .Unprotect Password:="mozzer"
        For Each xpt In .PivotTables
            xpt.RefreshTable
        Next xpt
        .Protect Password:="mozzer", _
        AllowUsingPivotTables:=True
    End With
Next

Hello I tried this and I get an error message, stating a sheet is protected. The first worksheet unlocked, but the second one did not. When I selected the "Debug" option

 .xpt.RefreshTable

was highlighted.
I tried to replace

With Activesheet

with

With shts("Pallet Totals", "Data Records")

and I get an "script out of range code".

I know we're getting a lot closer.

Thank you,

 

[breynolds0431]

Hello I tried this and I get an error message, stating a sheet is protected. The first worksheet unlocked, but the second one did not. When I selected the "Debug" option

 .xpt.RefreshTable

was highlighted.
I tried to replace

With Activesheet

with

With shts("Pallet Totals", "Data Records")

and I get an "script out of range code".

I know we're getting a lot closer.

Thank you,

Sounds like the protection error is due to the two sheet's Pivots using the same source data.

If the above is the same error that you received, then the sheet-unprotect must occur before the pivot refresh. Try the following:

Dim shts, i As Long, sh As Worksheet
shts = Array("data1", "Data Records") 'Revise data1 to actual name

'Unprotects sheets in array
For Each sh In Worksheets(shts)
    sh.Unprotect "mozzer"
Next sh

'Updates pivots of sheets in array
For i = LBound(shts) To UBound(shts)
    Call Reset
    Dim xpt As PivotTable
    With ActiveSheet
        For Each xpt In .PivotTables
            xpt.RefreshTable
        Next xpt
    End With
Next

'Protects sheets in array
For Each sh In Worksheets(shts)
    sh.Protect "mozzer", _
        AllowUsingPivotTables:=True
Next sh

 

[Livin404]

Thank you so much for your assistance and especially your patience, I tried that. Though I did not get any error, it didn't seem to want to refresh.
As I was playing with your earlier codes which also yield no results or errors. I thought maybe I can keep the code very basic, one that I'm not instructing it to loop through every sheet individually. I know the I need to write the unprotect / protect different. I know this is frustrating to you, and I do feel guilty about it.

You're very kind.

I thought about using 
[CODE=vba]Private Sub CmdRefresh_click()
Call Reset
ThisWorkbook.Unprotect "mozzer"
     ThisWorkbook.RefreshAll
    
    ThisWorkbook.Protect "mozzer"
    
End Sub

[/CODE]

 

[breynolds0431]

Thank you so much for your assistance and especially your patience, I tried that. Though I did not get any error, it didn't seem to want to refresh.
As I was playing with your earlier codes which also yield no results or errors. I thought maybe I can keep the code very basic, one that I'm not instructing it to loop through every sheet individually. I know the I need to write the unprotect / protect different. I know this is frustrating to you, and I do feel guilty about it.

You're very kind.

I thought about using
[CODE=vba]Private Sub CmdRefresh_click()
Call Reset
ThisWorkbook.Unprotect "mozzer"
     ThisWorkbook.RefreshAll
   
    ThisWorkbook.Protect "mozzer"
   
End Sub

[/CODE]

Nice. That worked for you? I thought .RefreshAll was for external links for some reason. Learning experience for both of us.

 

[Livin404]

Sounds like the protection error is due to the two sheet's Pivots using the same source data.

View attachment 73678

If the above is the same error that you received, then the sheet-unprotect must occur before the pivot refresh. Try the following:

Dim shts, i As Long, sh As Worksheet
shts = Array("data1", "Data Records") 'Revise data1 to actual name

'Unprotects sheets in array
For Each sh In Worksheets(shts)
    sh.Unprotect "mozzer"
Next sh

'Updates pivots of sheets in array
For i = LBound(shts) To UBound(shts)
    Call Reset
    Dim xpt As PivotTable
    With ActiveSheet
        For Each xpt In .PivotTables
            xpt.RefreshTable
        Next xpt
    End With
Next

'Protects sheets in array
For Each sh In Worksheets(shts)
    sh.Protect "mozzer", _
        AllowUsingPivotTables:=True
Next sh

I think I got it to work. Now I just got to find out what little boxes do I need to tick when I protect to make allow changes since everything now is going to be protected. The hang-up I'm having now is, those little boxes gets cleared out every-time I run the macro. In addition, I have a private sub that resizes and caps my data no longer works. I'm happy as I'm sure now it is something simple.

Thank you,

Private Sub CmdRefresh_click()
Call Reset
Dim ws As Worksheet
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
    ws.Unprotect Password:="mozzer"
Next ws
ActiveWorkbook.RefreshAll
For Each ws In ActiveWorkbook.Worksheets
  ws.Protect Password:="mozzer", _
        AllowUsingPivotTables:=True
Next ws
    
End Sub

 

[Livin404]

Nice. That worked for you? I thought .RefreshAll was for external links for some reason. Learning experience for both of us.

I finally go it I think, and you're right you got me going in the right direction by introducing the array. Thank you,

Private Sub CmdRefresh_click()
  Dim ws As Worksheet
  Dim Items, Item
 
  Items = Array("Inbound", "Data Records")
 
  For Each Item In Items
    Set ws = Worksheets(Item)
    ws.Unprotect Password:="mozzer"
  Next

  Call Reset
  ActiveWorkbook.RefreshAll
 
  For Each Item In Items
    Set ws = Worksheets(Item)
    ws.Protect Password:="mozzer", AllowUsingPivotTables:=True
  Next
End Sub