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

 

[bobsan42]

ThisWorkbook is an object which means "the Workbook that contains the code" or in your case the Personal Macro Workbook.
If you want to apply the code on another workbook you will have to replace ThisWorkbook with the appropriate reference e.g. ActiveWorkbook or Workbooks("workbook name").

 

[Tigerexcel]

ThisWorkbook is an object which means "the Workbook that contains the code" or in your case the Personal Macro Workbook.
If you want to apply the code on another workbook you will have to replace ThisWorkbook with the appropriate reference e.g. ActiveWorkbook or Workbooks("workbook name").

Thank you bobsan.
I tend to have a number of pivotables in each workbook, how do you notate each one so that the macro knows how to refer to them all? Also what if the Sheet with the pivot table is not named Sheet2? Do you use some sort of Activesheet reference?

 

[Michael M]

Use
Activeworkbook
AND
Activesheet

 

[Tigerexcel]

H there Mike,

I've made the following changes, the parts in yellow I'm unsure about still.

Sub AdjustPivotData_v1()

Dim Data_sht As Worksheet
Dim Pivot_sht As PivotTable
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 = ActiveSheet
Set Pivot_sht = ActiveSheet' Does the reference to 2 activesheets confuse Excel?

'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

 

[Michael M]

Set Pivot_sht = ActiveSheet' Does the reference to 2 activesheets confuse Excel?

Well, you can't have 2 activesheets ...so, yes it wouldn't work !!
If there going to be issues between 2 macros, why not simply have 2 of them in the PersonelMacroWorkbook, with appropriate references in each one !!

 

[Tigerexcel]

Set Pivot_sht = ActiveSheet' Does the reference to 2 activesheets confuse Excel?

Well, you can't have 2 activesheets ...so, yes it wouldn't work !!
If there going to be issues between 2 macros, why not simply have 2 of them in the PersonelMacroWorkbook, with appropriate references in each one !!

Ok, can the macro that I provided be adapted (with the correct coding) so that it does what I need it to do which is to expand the data range on a sheet while refreshing the pivot table?

 

[My Aswer Is This]

I have a lot of Macros in my Personal Workbook.
And if I need things to be dynamic like using different sheet names.
I Put a Userform in My Personal WorkBook and then use Textboxes on my Userform to enter things like sheet names.
Like Sheets Texbox1.value Range("A1") Value= "Alpha"
But that is just how I do things.
Then using a Shortcut key I open my Userform fill in my data and presto push a button and my script runs.

 

[MARK858]

Maybe in this case....

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

Assuming that Data_sht is the ActiveSheet when the code is run.

 

[Tigerexcel]

Maybe in this case....

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

Assuming that Data_sht is the ActiveSheet when the code is run.

Hi Mark,

Getting a run-time error 9 on the Set Pivot_sht =Sheets (ActiveSheet.index +1) line, can't see what the issue is as code looks fine.