Calling all Forum Gurus and anyone that can provide some ideas...

I have attached a workbook similar to the ones I am working with and trying to get some Macros created. I am trying to create a macro that will include formatting the worksheet and create a pivot table with a calculated field. I have the following macro that worked on a previous workbook, and when I copied from that workbook to the new workbook, I get an error.

The code I have been using for this macro is....

Sub CreateAPivotTable()

    Dim shtSource        As Worksheet
    Dim rngSource        As Range
    Dim rngDest          As Range
    Dim pvt              As PivotTable

    On Error GoTo ErrHandler

    'this prevents the screen from updating while the macro is running and
    'will make the code run faster
    Application.ScreenUpdating = False


    Set shtSource = ActiveSheet

    If shtSource.Name <> "Summary" Then

        'Rather than have the pivot table use all rows in column A-N
        'just use what has actually been used.
        Set rngSource = shtSource.Range("A1").CurrentRegion

        'This is where the pivot table will be placed
        Set rngDest = shtSource.Range("E1")

        'This creates a pivot table. So rather than having to refer to PivotTables("PivotTable14") like before you can just refer to pvt
        Set pvt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngSource, _
                                Version:=xlPivotTableVersion12).CreatePivotTable(TableDestination:=rngDest, _
                                DefaultVersion:=xlPivotTableVersion12)

        pvt.AddDataField pvt.PivotFields("Serial Rcvd"), "Count of Serial Rcvd", xlCount
        pvt.AddDataField pvt.PivotFields("Serial Shipped"), "Count of Serial Shipped", xlCount
        pvt.PivotFields("Count of Serial Rcvd").Caption = " Serial Rcvd"
        pvt.PivotFields("Count of Serial Shipped").Caption = " Serial Shipped"
        
        With pvt.PivotFields("Item")
            .Orientation = xlRowField
            .Position = 1
        End With
  
        'Formatting
        pvt.TableStyle2 = "PivotStyleDark7"
        With shtSource.Cells.Font
            .Name = "Calibri"
            .Size = 8
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
        End With

        ActiveWorkbook.ShowPivotTableFieldList = False
    Else
    MsgBox ("Please remove existing sheet with the name of Summary")

    End If

    Application.ScreenUpdating = True

    Exit Sub

    'Simple error handler in case something goes wrong
ErrHandler:
    Application.ScreenUpdating = True
    MsgBox "An error occurred: " & Err.Description, vbExclamation, "Error"


End Sub
The error I get reads...
An Error occurred: The PivotTable Field name is not valid. To create a PivotTable Report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.

The headers on the worksheet are labeled the same as in the code. I have tried numerous name changes but I am apparently missing something because it keeps giving me this error.

I also want to be able to create a code that will highlight duplicate specific to the data in column A.

For Example:
In A2:A6, I have the Item number, MC75 MET DEVICE (NS) with the serial numbers received in column B and when we ship back to the customer, the serial shipped goes in column C(probably not in same order as in Column B). The range can vary from day to day for the quantity of devices that get received from the customer(One day can receive 1 device and then the next day can receive 10). Then I need the next Item number set for duplicates the same way and this to happen for each Item or group of Identical items on the worksheet.

I have been doing this manually through conditional formatting but would REALLY love if I could do this with a Macro.


Any help or direction would be GREATLY appreciated!!!!



Thanks!