Need help with a Pivot Table Macro from another Workbook

[aperaza]

Hey everyone,

I'm working on getting this code to work from another workbook. The code works flawlessly on its own when used within the same workbook. My struggle is that I have to export new workbooks every day from our ERP system, but I do not want to copy and paste the code into the new workbook every single time. Plus, it makes it easier for my coworkers if it stays in the same place.

I worked around this by placing all my codes in a master workbook('HTS Commodities Codes'), which we all share for information, and then call upon macros in the new workbook that automate the vlookups and also to format the data for the pivot table. The error I keep receiving is 'Subscript is out of range.' So I know it has to do with the source, but any changes I make do not help. The first answer is obviously get a new ERP system, however, I just need to deal with this for another year before it becomes a reality.

Master Workbook: 'HTS Commodity Codes'
-has all the codes in "This Workbook"

New Workbooks: Names are Order Numbers. Worksheets are always named “SQL QUERY”

Any help is greatly appreciated!

Sub CreatePivot()
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
 
Set WSD = Worksheets("SQL QUERY")
Dim WSR As Worksheet
 
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)
 
 
Set PT = PTCache.CreatePivotTable(TableDestination:=Worksheets("SQL QUERY").Range("N2"))
 
 
PT.ManualUpdate = True
 
 
PT.AddFields RowFields:="HTS2"
 
 
With PT.PivotFields("QUANTITY")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "#,##0"
.Name = "Sum of Quantity"
End With
 
With PT.PivotFields("AMOUNT")
.Orientation = xlDataField
.Function = xlSum
.Position = 2
.NumberFormat = "$ #,##0.00"
.Name = "Sum of Amount"
End With
 
PT.NullString = "0"
 
PT.ManualUpdate = False
PT.ManualUpdate = True
 
 
End Sub