VBA to Setup Pivot Tables Not Working

[nirvehex]

Hi,

I have VBA code that sets up a pivot table, but for some reason my range is not selecting properly. It is supposed to select the range starting in cell A1 to last column and last row. But instead it is selecting A:AS.

Here is the code. The part that is not working properly is "PivotTable3"

Sub SetupDSPTabandPivots()


'DSPTab Setup


'Filter
 Worksheets("WOs").Range("A1").AutoFilter _
  Field:=16, Criteria1:="=DSP" _
        , Operator:=xlOr, Criteria2:="=REC"




'Paste Data from WO's Tab to WOs (DSP Only) tab


    Sheets("WOs").Select
    Range("A1").Select
    ActiveSheet.Range(Selection, Cells(Selection.End(xlDown).Row, Selection.End(xlToRight).Column)).Select
    Selection.Copy
    Sheets("WOs (DSP Only)").Select
    Range("G1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        
'Clear Filter


Worksheets("WOs").Range("A1").AutoFilter


'Setup Formula Ranges


Dim LastRow As Long
Application.ScreenUpdating = False
Sheets("WOs (DSP Only)").Select
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
LastRow = Selection.Count + 1


        
'Paste Unique Key & Array Percentile Forumlas


With Sheets("WOs (DSP Only)")


    .Range("A2").Formula = "=L2&""-""&K2&""-""&Z2&""-""&X2"
    .Range("A2").AutoFill Destination:=.Range("A2:A" & LastRow)
    .Range("B2").FormulaArray = "=PERCENTILE.INC(IF($A$2:$A$" & LastRow & "=A2,$AF$2:$AF$" & LastRow & "),.5)"
    .Range("B2").AutoFill Destination:=.Range("B2:B" & LastRow)
    
    .Range("C2").FormulaArray = "=PERCENTILE.INC(IF($A$2:$A$" & LastRow & "=A2,$AF$2:$AF$" & LastRow & "),.65)"
    .Range("C2").AutoFill Destination:=.Range("C2:C" & LastRow)
    
    .Range("D2").FormulaArray = "=PERCENTILE.INC(IF($A$2:$A$" & LastRow & "=A2,$AF$2:$AF$" & LastRow & "),.75)"
    .Range("D2").AutoFill Destination:=.Range("D2:D" & LastRow)
    
    .Range("E2").FormulaArray = "=PERCENTILE.INC(IF($A$2:$A$" & LastRow & "=A2,$AF$2:$AF$" & LastRow & "),.9)"
    .Range("E2").AutoFill Destination:=.Range("E2:E" & LastRow)
    
    .Range("F2").FormulaArray = "=PERCENTILE.INC(IF($A$2:$A$" & LastRow & "=A2,$AF$2:$AF$" & LastRow & "),.99)"
    .Range("F2").AutoFill Destination:=.Range("F2:F" & LastRow)
    
    End With
    
'Setting Up Pivot Tables


Dim Data_sht1 As Worksheet
Dim Data_sht2 As Worksheet
Dim Pivot_sht As Worksheet
Dim StartPoint As Range
Dim StartPoint2 As Range
Dim DataRange As Range
Dim DataRange2 As Range
Dim PivotName As String
Dim NewRange As String
Dim NewRange2 As String


Set Data_sht1 = ThisWorkbook.Worksheets("WOs")
Set Data_sht2 = ThisWorkbook.Worksheets("WOs (DSP Only)")
Set Pivot_sht = ThisWorkbook.Worksheets("Pivot")


'PivotTable1 Setup


PivotName = "PivotTable1"


Set StartPoint = Data_sht1.Range("A1")
Set DataRange = Data_sht1.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))


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


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


Pivot_sht.PivotTables("PivotTable1").RefreshTable


'PivotTable3 Setup


PivotName = "PivotTable3"


Set StartPoint2 = Data_sht2.Range("A1")
Set DataRange2 = Data_sht2.Range(StartPoint2, StartPoint2.SpecialCells(xlLastCell))


NewRange2 = Data_sht2.Name & "!" & _
DataRange2.Address(ReferenceStyle:=xlR1C1)


Pivot_sht.PivotTables("PivotTable3").ChangePivotCache _
ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=NewRange2)


Pivot_sht.PivotTables("PivotTable3").RefreshTable


'PivotTable4 Setup


PivotName = "PivotTable4"


Set StartPoint = Data_sht1.Range("A1")
Set DataRange = Data_sht1.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))


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


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


Pivot_sht.PivotTables("PivotTable4").RefreshTable
 
        
End Sub

Any help would be greatly appreciated.

Thank you!