Macro keeps braking when creating PT - cannot find the source

[Jenya]

Hello,

I have written the macro below. I saved it first in the current workbook and after I have finished it I saved it in my personal workbook. It keeps breaking before it creates the Pivot table and it says it cannot find the source. I have to say that when I copied the data into the workbook where I have first written the macro it works OK. I don't know what the problem might be Could you please help me with this.

Sub Pipeline_macro()
'deletes runrate, labels the team and bands the deals

ActiveSheet.Name = "Pipeline"

Dim SearchCol1 As String
SearchCol1 = "Opportunity Name"

Dim SearchCol2 As String
SearchCol2 = "Account Name"

Dim lrow As Long
lrow = Cells(Rows.Count, 1).End(xlUp).Row

Dim lcol As Long
lcol = Cells(1, Columns.Count).End(xlToLeft).Column

Dim rng1 As Range
Set rng1 = ActiveSheet.UsedRange.Find(SearchCol1, , xlValues, xlWhole)

Dim rng2 As Range
Set rng2 = ActiveSheet.UsedRange.Find(SearchCol2, , xlValues, xlWhole)

Dim PRange As Range
Set PRange = Worksheets("Pipeline").Cells(1, 1).Resize(lrow, lcol)

Dim PTCache As PivotCache
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)

Dim PT As PivotTable

'delete runrate from Opportunity name column
Range("$A$1").CurrentRegion.AutoFilter Field:=rng1.Column, Criteria1:=Array("*runrate*", "*runnrate*"), _
        Operator:=xlFilterValues
        Range("A1").CurrentRegion.Offset(1).EntireRow.Delete
        Range("A1").AutoFilter
    Range("$A$1").CurrentRegion.AutoFilter Field:=rng1.Column, Criteria1:=Array("*run-rate*", "*run rate*"), _
        Operator:=xlFilterValues
        Range("A1").CurrentRegion.Offset(1).EntireRow.Delete
        Range("A1").AutoFilter

'delete runrate from Account name column
Range("$A$1").CurrentRegion.AutoFilter Field:=rng2.Column, Criteria1:=Array("*runrate*", "*runnrate*"), _
        Operator:=xlFilterValues
        Range("A1").CurrentRegion.Offset(1).EntireRow.Delete
        Range("A1").AutoFilter
    Range("$A$1").CurrentRegion.AutoFilter Field:=rng2.Column, Criteria1:=Array("*run-rate*", "*run rate*"), _
        Operator:=xlFilterValues
        Range("A1").CurrentRegion.Offset(1).EntireRow.Delete
        Range("A1").AutoFilter
        
 'Insert new column before 06.District/Team and label the Teams
Cells.Find("06. District / Team", , xlValues, xlWhole).EntireColumn.Insert
Cells.Find("06. District / Team", , xlValues, xlWhole).EntireColumn.FindPrevious.Activate
ActiveCell.Offset(0, -1).Formula = "Team"
ActiveCell.Offset(1, -1).FormulaR1C1 = "=IF(LEFT(RC[1],6)=""AT_COM"",""AUSTRIA"",IF(LEFT(RC[1],6)=""BE_COM"",""BELUX"",IF(LEFT(RC[1],6)=""CP_COM"",""CZECH"",IF(LEFT(RC[1],6)=""CZ_COM"",""CZECH"",IF(LEFT(RC[1],6)=""DK_COM"",""DENMARK"",IF(LEFT(RC[1],6)=""FI_COM"",""FINLAND"",IF(LEFT(RC[1],6)=""FR_COM""," & _
"""FRANCE"",IF(LEFT(RC[1],6)=""DE_COM"",""GERMANY"",IF(LEFT(RC[1],6)=""GR_COM"",""GREECE"",IF(LEFT(RC[1],6)=""IL_COM"",""ISRAEL"",IF(LEFT(RC[1],6)=""IT_COM"",""ITALY"",IF(LEFT(RC[1],6)=""ME_COM"",""MIDDLE EAST"",IF(LEFT(RC[1],6)=""NL_COM"",""NETHERLANDS"",IF(LEFT(RC[1],6)=""NO_COM"",""NORWAY"",IF(LEFT(RC[1],6)=""PL_COM"",""POLAND"",IF(LEFT(RC[1],6)=""PT_COM"",""PORTUGAL"",IF(LEFT(RC[1],6)=""RU_RUSSIA"",""RUSSIA"",IF(LEFT(RC[1],6)=""RU_ENT"",""RUSSIA"",IF(LEFT(RC[1],6)=""SEE_CO"",""SEE"",IF(LEFT(RC[1],6)=""ES_COM"",""SPAIN"",IF(LEFT(RC[1],6)=""SA_COM"",""SOUTH AFRICA"",IF(LEFT(RC[1],6)=""SE_COM"",""SWEDEN"",IF(LEFT(RC[1],6)=""CH_COM"",""SWITZERLAND"",IF(LEFT(RC[1],6)=""TR_COM"",""TURKEY"",IF(LEFT(RC[1],6)=""UK_COM"",""UK"",IF(LEFT(RC[1],6)=""UK_ENT"",""UK PS"",""UNKNOWN""))))))))))))))))))))))))))"

'Copy formula down
ActiveCell.Offset(1, -1).Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A" & lrow - 1)


'paste special values
Cells.Find("Team", , xlValues, xlWhole).EntireColumn.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'cut the column and paste special value after the last column with data
Cells.Find("Team", , xlValues, xlWhole).EntireColumn.Cut Cells(1, lcol + 2)
ActiveCell.EntireColumn.Delete

'Insert new column before Territory Name and clean the codes
Cells.Find("Territory Name", , xlValues, xlWhole).EntireColumn.Insert
Cells.Find("Territory Name", , xlValues, xlWhole).EntireColumn.FindPrevious.Activate
ActiveCell.Offset(0, -1).Formula = "Sales Territory"
ActiveCell.Offset(1, -1).FormulaR1C1 = "=TRIM(IF((IFERROR(SEARCH(""CONT"",RC[1]),0)>0),(LEFT(RC[1],(LEN(RC[1])-13))),RC[1]))"

'Copy formula down
ActiveCell.Offset(1, -1).Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A" & lrow - 1)
       
'paste special values
Cells.Find("Sales Territory", , xlValues, xlWhole).EntireColumn.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'cut the column and paste special value after the last column with data
Cells.Find("Sales Territory", , xlValues, xlWhole).EntireColumn.Cut Cells(1, lcol + 3)
ActiveCell.EntireColumn.Delete
       
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Pivot"

       
'create PT to find the value of each deal
Set PT = PTCache.CreatePivotTable(TableDestination:="Pivot!R1C1", TableName:="PivotTable1")

PT.AddFields RowFields:="Deal ID"
With PT.PivotFields("Total Price (converted)")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.Name = "revenue"
End With

Dim lrowPT As Long
lrowPT = Cells(Rows.Count, 1).End(xlUp).Row
ActiveCell.Offset(2, 2).FormulaR1C1 = "=IF(ABS(RC[-1])<3000,""SUB-3K"",IF(AND(ABS(RC[-1])>=3000,ABS(RC[-1])<=7000),""$3K to $7K"",IF(AND(ABS(RC[-1])>=7000,ABS(RC[-1])<=50000),""$7K to $50K"","">$50K"")))"
ActiveCell.Offset(2, 2).Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A" & lrowPT - 3)
Range("A1:C" & lrowPT).Name = "PivotData"

'lookup the banding from PT and label the deals on the Pipeline sheet

Sheets("Pipeline").Select
Cells.Find("Deal ID", , xlValues, xlWhole).EntireColumn.Insert
Cells.Find("Deal ID", , xlValues, xlWhole).EntireColumn.Activate
ActiveCell.Offset(0, -1).Formula = "Total Band"
ActiveCell.Offset(1, -1).FormulaR1C1 = "=VLOOKUP(RC[1],PivotData,3,0)"
ActiveCell.Offset(1, -1).Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A" & lrow - 1)
ActiveCell.EntireColumn.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
ActiveCell.EntireColumn.Cut Cells(1, lcol + 4)
ActiveCell.EntireColumn.Delete
       
'delete Pivot Sheet
Sheets("Pivot").Delete

       
End Sub