Having hard time getting formatting correct

Alright Excel Forum users and Gurus!

I have attached the sample workbook I have been working with for all my codes. I am trying to create a calculated column that takes Serial Rcvd - Serial Shipped = Devices Owed. Just like on the other worksheets in the workbook. However, the code seems to get stuck in the loop and also does not even do calculation.

This is the code I currently have for the calculations...

Sub AddNewCol()

    Dim WSD As Worksheet
    Dim PVT As PivotTable
    Dim PVTRows As Long
    Dim PVTCols As Long
    Dim NewRow As Long
    Dim FirstCol As Long
    
    Application.ScreenUpdating = False
    Set WSD = ActiveSheet

    For Each PVT In WSD.PivotTables
        WSD.PivotTables(PVT.Name).PivotSelect "", xlDataAndLabel, True
        
        PVTRows = Selection.Rows.Count
        PVTCols = Selection.Columns.Count
        FirstCol = Selection.Column
        
        WSD.Cells(1, PVTCols + FirstCol).EntireColumn.Clear
        
        WSD.Cells(1, PVTCols + FirstCol) = "Devices Owed"
        
        For NewRow = 2 To PVTRows
            WSD.Cells(NewRow, PVTCols + FirstCol).FormulaR1C1 = _
            "=IFERROR(GETPIVOTDATA("" Serial Rcvd"",R2C" & FirstCol & ",""Item Number"",RC" & FirstCol & ")-GETPIVOTDATA("" Serial Shipped"",R2C" & FirstCol & ",""Item Number"",RC" & FirstCol & "),"""")"
        Next NewRow
        
        WSD.Range(Cells(2, FirstCol + 1), Cells(PVTRows + 1, FirstCol + 1)).copy
        WSD.Range(Cells(2, FirstCol + PVTCols), Cells(PVTRows + 1, FirstCol + PVTCols)).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        
        WSD.Cells(PVTRows + 1, FirstCol + PVTCols).FormulaR1C1 = "=SUM(R4C" & FirstCol + PVTCols & ":R" & PVTRows & "C" & FirstCol + PVTCols & ")"
        
        If WSD.Cells(PVTRows + 1, FirstCol + PVTCols).Value = 0 Then
            WSD.Tab.Color = 5296274
        Else
            WSD.Tab.ColorIndex = xlColorIndexNone
        End If
        
        WSD.Range("A1").Select
    Next PVT
    Application.ScreenUpdating = True

       
End Sub

I also what to change the destination of the pivot table from the Master Data Collection worksheet to the Summary Report Worksheet.

This is the code I have for the pivot table

Sub CreatePivotTable()

    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PVT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long
    Dim PVTRows As Long
    Dim PVTCols As Long
    Dim NewRow As Long
    
    Application.ScreenUpdating = False
    Set WSD = ActiveSheet
    
    For Each PVT In WSD.PivotTables
        PVT.TableRange2.Clear
    Next PVT
    
    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 PVT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, FinalCol + 3), TableName:="PivotTable1")
    
    With PVT.PivotFields("Item")
            .Orientation = xlRowField
            .Position = 1
    End With
    
    With PVT.PivotFields("RSA")
        .Orientation = xlRowField
        .Position = 2
    End With
    
    With PVT
        .ManualUpdate = True
        
        .AddDataField .PivotFields("Serial Rcvd"), " Serial Rcvd", xlCount
        .AddDataField .PivotFields("Serial Shipped"), " Serial Shipped", xlCount
        .PivotFields(" Serial Rcvd").NumberFormat = "#,##0"
        .PivotFields(" Serial Shipped").NumberFormat = "#,##0"
      
      
        .ShowTableStyleColumnStripes = True
        .ShowTableStyleRowStripes = True
        .TableStyle2 = "PivotStyleDark7"
        .ColumnGrand = True
        .RowGrand = False
        .RepeatAllLabels xlRepeatLabels
        .DataPivotField.Orientation = xlColumnField
        .ManualUpdate = False
    End With
    
    Call AddNewCol
    
    Application.ScreenUpdating = True
End Sub

Let me know how to resolve these issues.

Also...does anyone know how I can create a code that will import an attachment from Outlook into this workbook?



Thanks!