Why does this work in its own file but not in Personal Macro Workbook?

[Tigerexcel]

The following macro works fine if I copy it into the particular file that I use it in, but it won't work if I try to run it from the Personal Macro Workbook because it can't find Sheet2 in the Personal Macro Workbook. It therefore gives run time error 9. What do I change this to so that it will work in any file as updating pivot tables is something I do regularly across different files?

Sub AdjustPivotData()

Dim Data_sht As Worksheet
Dim Pivot_sht As Worksheet
Dim StartPoint As Range
Dim DataRange As Range
Dim PivotName As String
Dim NewRange As String

'Set Variables Equal to Data Sheet and Pivot Sheet
Set Data_sht = ThisWorkbook.Worksheets("Sheet1")
Set Pivot_sht = ThisWorkbook.Worksheets("Sheet2")

'Enter in Pivot Table Name
PivotName = "PivotTable3"

'Dynamically Retrieve Range Address of Data
Set StartPoint = Data_sht.Range("A1")
Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))

NewRange = Data_sht.Name & "!" & _
DataRange.Address(ReferenceStyle:=xlR1C1)

'Make sure every column in data set has a heading and is not blank (error prevention)
If WorksheetFunction.CountBlank(DataRange.Rows(1)) > 0 Then
MsgBox "One of your data columns has a blank heading." & vbNewLine _
& "Please fix and re-run!.", vbCritical, "Column Heading Missing!"
Exit Sub
End If

'Change Pivot Table Data Source Range Address
Pivot_sht.PivotTables(PivotName).ChangePivotCache _
ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=NewRange)

'Ensure Pivot Table is Refreshed
Pivot_sht.PivotTables(PivotName).RefreshTable

'Complete Message
MsgBox PivotName & "'s data source range has been successfully updated!"

End Sub

 

[MARK858]

Are you sure that you have a sheet after the ActiveSheet? as the code below works fine for me

Sub xxx()
Dim Data_sht As Worksheet, Pivot_sht As Worksheet
Set Data_sht = ActiveSheet
Set Pivot_sht = Sheets(ActiveSheet.Index + 1)
MsgBox Pivot_sht.Name
End Sub

 

[Tigerexcel]

Are you sure that you have a sheet after the ActiveSheet? as the code below works fine for me

Sub xxx()
Dim Data_sht As Worksheet, Pivot_sht As Worksheet
Set Data_sht = ActiveSheet
Set Pivot_sht = Sheets(ActiveSheet.Index + 1)
MsgBox Pivot_sht.Name
End Sub

Initially the Pivot table sheet was on the left of the Source Data sheet. I moved the Pivot table sheet after the Source Data sheet, but I 'm still getting the same error message. This particular file does have one other worksheet in it as well as a couple of other pivot tables, I wonder if that is causing a conflict? I receive the file in this manner from a centralised source so have little say in how I get it.

 

[MARK858]

I moved the Pivot table sheet after the Source Data sheet, but I 'm still getting the same error message.

I only get that error on that line if there is no sheet after the ActiveSheet, if there is a sheet after the ActiveSheet then I get no error.

If your sheets come in varying orders and with various names then I am afraid I don't see how we can come up with a generic solution other than having an input box for you to manually input the name each time.

 

[Tigerexcel]

I only get that error on that line if there is no sheet after the ActiveSheet, if there is a sheet after the ActiveSheet then I get no error.

If your sheets come in varying orders and with various names then I am afraid I don't see how we can come up with a generic solution other than having an input box for you to manually input the name each time.

Thanks Mark for your patience, I amended the code after getting a Type Mismatch error to Dim Pivot_sht As Worksheet, this allowed me to keep going through the macro but I'm getting the Dialog box warning that I have my source data is missing a column heading, I can guarantee that all the headings are there.
I have deleted the extraneous sheets from the file to just the source file and the pivot table. If possible I'd like to at least get the macro for files that have one worksheet and one pivot table.

 

[bobsan42]

Here is a piece of code I use to update all pivot tables cache in the active workbook:

Sub updateAllPivotTables()
    On Error GoTo EP
    Dim wb As Workbook: Set wb = ActiveWorkbook
    Dim wsh As Worksheet
    Dim pt As PivotTable
    Dim i As Integer
    
    For Each wsh In wb.Worksheets
        For Each pt In wsh.PivotTables
            pt.PivotCache.Refresh
            i = i + 1
        Next pt
    Next wsh
    
EP:
    MsgBox "workbook: " & wb.Name & vbCrLf & i & " Pivot tables refreshed.", vbOKOnly, glConstAppTitle
    Set wb = Nothing
    Set wsh = Nothing
    Set pt = Nothing

End Sub

The code is in a addin file (similar to to your personal macro wb).
Adapt it to your needs.

 

[bobsan42]

Thanks Mark for your patience, I amended the code after getting a Type Mismatch error to Dim Pivot_sht As Worksheet, this allowed me to keep going through the macro but I'm getting the Dialog box warning that I have my source data is missing a column heading, I can guarantee that all the headings are there.
I have deleted the extraneous sheets from the file to just the source file and the pivot table. If possible I'd like to at least get the macro for files that have one worksheet and one pivot table.

Just to make sure where you are always specify the workbook before the worksheet.
for example ActiveSheet is always in the Active workbook.
But sheet(5) can be in any of your open workbooks (I would bet on ThisWorkbook).
So the 100% correct way to specify the sheet is:

Set Data_sht = ActiveSheet
Set Pivot_sht = ActiveWorkbook.Sheets(ActiveSheet.Index + 1)

 

[MARK858]

Set Pivot_sht = ActiveWorkbook.Sheets(ActiveSheet.Index + 1)

The above bobsan42 is 100% correct is the correct full method of referring to the sheet but I wouldn't bet on

(I would bet on ThisWorkbook)

as the variable is being set the immediate line after the ActiveSheet is being set so it will be referring to the same workbook (as when the workbook is omitted it refers to the current ActiveWorkbook not ThisWorkbook).

The Subscript 9 error was caused by the worksheet being dimmed as PivotTable not WorkSheet.

 

[Tigerexcel]

Have made the amendments above regarding the set Pivot_sht and also dimmed it as worksheet, the macro now advances to

Pivot_sht.PivotTables(PivotName).ChangePivotCache _
ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=NewRange)

where it returns an invalid procedure call or argument.

 

[bobsan42]

for a start replace ThisWorkbook with ActiveWorkbook (do this everywhere in your code if you run it from the personal macro workbook against other files)

Pivot_sht.PivotTables(PivotName).ChangePivotCache _
ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=NewRange)