VBA Pivot Table Code Error Please Help

rkol297

rkol297

Board Regular
Joined
Nov 12, 2010
Messages
131
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have separate tabs for each state in the US, with data sorted to shipment for only that state. When there is a shipment listed in the rows for the state the code below creates the Pivot Table perfectly fine. However, as the day goes on and shipments are completed not all states will have a pending shipment listed on the tab for the state. I still need the macro to create a pivot table even if its blank so that the tabs after that may have shipments will continue to be created. When I'm running into is that when it gets to a state that only has the header and no shipment data it resumes on next and then the subsequent tabs for the other states become messed up. Any idea how I can make excel create a blank pivot table just so that the subsequent tabs that have shipments don't get messed up?

Code: 
'###HI-65M###</SPAN>
    Sheets("HI-65-MDATA").Select</SPAN>
    Range("A1").Select</SPAN>
    Dim objTableHI65M As PivotTable, objFieldHI65M As PivotField</SPAN>
    ActiveWorkbook.Sheets("HI-65-MDATA").Select</SPAN>
    Range("A1").Select</SPAN>
    Set objTableHI65M = Sheets("HI-65-MDATA").PivotTableWizard</SPAN>
    Set objFieldHI65M = objTableHI65M.PivotFields("TRC")</SPAN>
    objFieldHI65M.Orientation = xlColumnField</SPAN>
    Set objFieldHI65M = objTableHI65M.PivotFields("DAY")</SPAN>
        objFieldHI65M.PivotItems("PREVIOUS SHIP").Visible = True</SPAN>
        objFieldHI65M.PivotItems("FUTURE SHIP").Visible = False</SPAN>
        objFieldHI65M.PivotItems("NEXT DAY").Visible = False</SPAN>
    objFieldHI65M.Orientation = xlRowField</SPAN>
    objFieldHI65M.PivotItems( _</SPAN>
        "SAME DAY").Position = 1</SPAN>
    objFieldHI65M.PivotItems( _</SPAN>
        "PREVIOUS SHIP").Position = 1</SPAN>
    objFieldHI65M.PivotItems( _</SPAN>
        "SUNDAY SHIP").Position = 1</SPAN>
    objFieldHI65M.PivotItems( _</SPAN>
        "SATURDAY SHIP").Position = 1</SPAN>
    Set objFieldHI65M = objTableHI65M.PivotFields("GROUPER")</SPAN>
    Dim varItemListHI65M() As Variant</SPAN>
    Dim strItem1HI65M As String</SPAN>
    Dim iHI65M As Long</SPAN>
    Application.ScreenUpdating = False</SPAN>
    varItemListHI65M = Array("REVA SUSP", "PAH INH", "PAH ORALS", "PAH INJ", "ZYMES", "HAE", "ALPHA-IG", "ACTI")</SPAN>
    strItem1HI65M = varItemListHI65M(LBound(varItemListHI65M))</SPAN>
    With objTableHI65M.PivotFields("GROUPER")</SPAN>
        .PivotItems(strItem1HI65M).Visible = True</SPAN>
        For iHI65M = 1 To .PivotItems.Count</SPAN>
            If .PivotItems(iHI65M) <> strItem1HI65M And _</SPAN>
                  .PivotItems(iHI65M).Visible = True Then</SPAN>
                .PivotItems(iHI65M).Visible = False</SPAN>
            End If</SPAN>
        Next iHI65M</SPAN>
        For iHI65M = LBound(varItemListHI65M) + 1 To UBound(varItemListHI65M)</SPAN>
            .PivotItems(varItemListHI65M(iHI65M)).Visible = True</SPAN>
        Next iHI65M</SPAN>
    End With</SPAN>
    objFieldHI65M.Orientation = xlRowField</SPAN>
    objFieldHI65M.PivotItems( _</SPAN>
        "ACTI").Position = 1</SPAN>
    objFieldHI65M.PivotItems( _</SPAN>
        "PAH ORALS").Position = 1</SPAN>
    objFieldHI65M.PivotItems( _</SPAN>
        "PAH INH").Position = 1</SPAN>
    objFieldHI65M.PivotItems( _</SPAN>
        "PAH INJ").Position = 1</SPAN>
    objFieldHI65M.PivotItems( _</SPAN>
        "ALPHA-IG").Position = 1</SPAN>
    objFieldHI65M.PivotItems( _</SPAN>
        "HAE").Position = 1</SPAN>
    objFieldHI65M.PivotItems( _</SPAN>
        "ZYMES").Position = 1</SPAN>
    objFieldHI65M.PivotItems( _</SPAN>
        "REVA SUSP").Position = 1</SPAN>
    Set objFieldHI65M = objTableHI65M.PivotFields("THERAPY TYPE")</SPAN>
    objFieldHI65M.Orientation = xlRowField</SPAN>
    Set objFieldHI65M = objTableHI65M.PivotFields("RX HOME ID #")</SPAN>
    objFieldHI65M.Orientation = xlRowField</SPAN>
    Set objFieldHI65M = objTableHI65M.PivotFields("RX HOME ID #")</SPAN>
    objFieldHI65M.Orientation = xlDataField</SPAN>
    objFieldHI65M.Function = xlCount</SPAN>
    objFieldHI65M.NumberFormat = "Number"</SPAN>
    objTableHI65M.RowAxisLayout xlOutlineRow</SPAN>
    objTableHI65M.TableStyle2 = "PivotStyleMedium9"</SPAN>
    Sheets("Sheet159").Select</SPAN>
    Sheets("Sheet159").Name = "HI-MEDCO"</SPAN>
    ActiveWorkbook.ShowPivotTableFieldList = False</SPAN>
    Range("A1").Select</SPAN>
'###HI-65M NEXT DAY PIVOT TABLE###</SPAN>
    Sheets("HI-65-MDATA").Select</SPAN>
    Range("A1").Select</SPAN>
    Dim objTableHI65MND As PivotTable, objFieldHI65MND As PivotField</SPAN>
    ActiveWorkbook.Sheets("HI-65-MDATA").Select</SPAN>
    Range("A1").Select</SPAN>
    Set objTableHI65MND = Sheets("HI-65-MDATA").PivotTableWizard</SPAN>
    Set objFieldHI65MND = objTableHI65MND.PivotFields("TRC")</SPAN>
    objFieldHI65MND.Orientation = xlColumnField</SPAN>
    Set objFieldHI65MND = objTableHI65MND.PivotFields("DAY")</SPAN>
        objFieldHI65MND.PivotItems("NEXT DAY").Visible = True</SPAN>
        objFieldHI65MND.PivotItems("PREVIOUS SHIP").Visible = False</SPAN>
        objFieldHI65MND.PivotItems("SAME DAY").Visible = False</SPAN>
        objFieldHI65MND.PivotItems("FUTURE SHIP").Visible = False</SPAN>
    objFieldHI65MND.Orientation = xlRowField</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "NEXT DAY").Position = 1</SPAN>
    Set objFieldHI65MND = objTableHI65MND.PivotFields("THERAPY TYPE")</SPAN>
    Dim varItemListHI65MND() As Variant</SPAN>
    Dim strItem1HI65MND As String</SPAN>
    Dim iHI65MND As Long</SPAN>
    Application.ScreenUpdating = False</SPAN>
    varItemListHI65MND = Array("SILD", "REVA", "REVG", "REGC", "ADCR", "LETA", "TPAP", "TRAC", "TRAB", "REVC", "TOBI", "PUL", "ACTP", "GMPX", "FLBO", "IVGF", "GKSC", "GKIV", "GGSC", "GGIV", "IVGS", "GAMS", "HIZA", "IVGP", "GAMC", "GLSA", "ZEMA", "ARAL", "REMP", "REMO", "RMIV", "RMIP", "TYVS", "TYVP", "TYVA", "VENT", "VENP", "FPAP", "EPOP", "EPAP", "EPAC", "F100", "FL50", "FZYR", "BERT", "ELAP", "ALDZ", "FABZ", "FABF", "NGLZ", "NGLF", "MYOZ", "LUMZ", "VPRV", "ZYMF", "ZYME")</SPAN>
    strItem1HI65MND = varItemListHI65MND(LBound(varItemListHI65MND))</SPAN>
    With objTableHI65MND.PivotFields("THERAPY TYPE")</SPAN>
        .PivotItems(strItem1HI65MND).Visible = True</SPAN>
        For iHI65MND = 1 To .PivotItems.Count</SPAN>
            If .PivotItems(iHI65MND) <> strItem1HI65MND And _</SPAN>
                  .PivotItems(iHI65MND).Visible = True Then</SPAN>
                .PivotItems(iHI65MND).Visible = False</SPAN>
            End If</SPAN>
        Next iHI65MND</SPAN>
        For iHI65MND = LBound(varItemListHI65MND) + 1 To UBound(varItemListHI65MND)</SPAN>
            .PivotItems(varItemListHI65MND(iHI65MND)).Visible = True</SPAN>
        Next iHI65MND</SPAN>
    End With</SPAN>
    objFieldHI65MND.Orientation = xlRowField</SPAN>
    objFieldHI65MND.Orientation = xlRowField</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "SILD").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "REVA").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "ADCR").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "LETA").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "TPAP").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "TRAC").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "TRAB").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "REVC").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "TOBI").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "PUL").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "ACTP").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "GMPX").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "FLBO").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "IVGF").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "GKSC").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "GKIV").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "GGSC").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "GGIV").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "IVGS").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "GAMS").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "HIZA").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "IVGP").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "GAMC").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "GLSA").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "ZEMA").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "ARAL").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "REMP").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "REMO").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "RMIV").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "RMIP").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "TYVS").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "TYVP").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "TYVA").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "VENT").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "VENP").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "FPAP").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "EPOP").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "EPAP").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "EPAC").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "F100").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "FL50").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "FZYR").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "BERT").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "ELAP").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "ALDZ").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "FABZ").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "FABF").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "NGLZ").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "NGLF").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "MYOZ").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "LUMZ").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "VPRV").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "ZYMF").Position = 1</SPAN>
    objFieldHI65MND.PivotItems( _</SPAN>
        "ZYME").Position = 1</SPAN>
    Set objFieldHI65MND = objTableHI65MND.PivotFields("RX HOME ID #")</SPAN>
    objFieldHI65MND.Orientation = xlRowField</SPAN>
    Set objFieldHI65MND = objTableHI65MND.PivotFields("RX HOME ID #")</SPAN>
    objFieldHI65MND.Orientation = xlDataField</SPAN>
    objFieldHI65MND.Function = xlCount</SPAN>
    objFieldHI65MND.NumberFormat = "Number"</SPAN>
    objTableHI65MND.RowAxisLayout xlOutlineRow</SPAN>
    objTableHI65MND.TableStyle2 = "PivotStyleMedium10"</SPAN>
    objTableHI65MND.Location = "'HI-MEDCO'!$J$1"</SPAN>
    Sheets("Sheet160").Select</SPAN>
    Sheets("Sheet160").Delete</SPAN>
    ActiveWorkbook.ShowPivotTableFieldList = False</SPAN>
    Sheets("HI-MEDCO").Select</SPAN>
    Columns("H:H").Select</SPAN>
    Selection.ColumnWidth = 1.1</SPAN>
    Columns("I:I").Select</SPAN>
    Selection.ColumnWidth = 1.1</SPAN>
    Cells.Select</SPAN>
    Selection.Rows.AutoFit</SPAN>
    Selection.Columns.AutoFit</SPAN>
    Range("A1").Select</SPAN>
'###HI-65M FUTURE PIVOT TABLE###</SPAN>
    Sheets("HI-65-MDATA").Select</SPAN>
    Range("A1").Select</SPAN>
    Dim objTableHI65MFD As PivotTable, objFieldHI65MFD As PivotField</SPAN>
    ActiveWorkbook.Sheets("HI-65-MDATA").Select</SPAN>
    Range("A1").Select</SPAN>
    Set objTableHI65MFD = Sheets("HI-65-MDATA").PivotTableWizard</SPAN>
    Set objFieldHI65MFD = objTableHI65MFD.PivotFields("TRC")</SPAN>
    objFieldHI65MFD.Orientation = xlColumnField</SPAN>
    Set objFieldHI65MFD = objTableHI65MFD.PivotFields("DAY")</SPAN>
        objFieldHI65MFD.PivotItems("FUTURE SHIP").Visible = True</SPAN>
        objFieldHI65MFD.PivotItems("NEXT DAY").Visible = False</SPAN>
        objFieldHI65MFD.PivotItems("PREVIOUS SHIP").Visible = False</SPAN>
        objFieldHI65MFD.PivotItems("SAME DAY").Visible = False</SPAN>
    objFieldHI65MFD.Orientation = xlRowField</SPAN>
   Set objFieldHI65MFD = objTableHI65MFD.PivotFields("SHIP DATE")</SPAN>
   objFieldHI65MFD.Orientation = xlRowField</SPAN>
   Set objFieldHI65MFD = objTableHI65MFD.PivotFields("THERAPY TYPE")</SPAN>
    Dim varItemListHI65MFD() As Variant</SPAN>
    Dim strItem1HI65MFD As String</SPAN>
    Dim iHI65MFD As Long</SPAN>
    Application.ScreenUpdating = False</SPAN>
    varItemListHI65MFD = Array("SILD", "REVA", "REVG", "REGC", "ADCR", "LETA", "TPAP", "TRAC", "TRAB", "REVC", "TOBI", "PUL", "ACTP", "GMPX", "FLBO", "IVGF", "GKSC", "GKIV", "GGSC", "GGIV", "IVGS", "GAMS", "HIZA", "IVGP", "GAMC", "GLSA", "ZEMA", "ARAL", "REMP", "REMO", "RMIV", "RMIP", "TYVS", "TYVP", "TYVA", "VENT", "VENP", "FPAP", "EPOP", "EPAP", "EPAC", "F100", "FL50", "FZYR", "BERT", "ELAP", "ALDZ", "FABZ", "FABF", "NGLZ", "NGLF", "MYOZ", "LUMZ", "VPRV", "ZYMF", "ZYME")</SPAN>
    strItem1HI65MFD = varItemListHI65MFD(LBound(varItemListHI65MFD))</SPAN>
    With objTableHI65MFD.PivotFields("THERAPY TYPE")</SPAN>
        .PivotItems(strItem1HI65MFD).Visible = True</SPAN>
        For iHI65MFD = 1 To .PivotItems.Count</SPAN>
            If .PivotItems(iHI65MFD) <> strItem1HI65MFD And _</SPAN>
                  .PivotItems(iHI65MFD).Visible = True Then</SPAN>
                .PivotItems(iHI65MFD).Visible = False</SPAN>
            End If</SPAN>
        Next iHI65MFD</SPAN>
        For iHI65MFD = LBound(varItemListHI65MFD) + 1 To UBound(varItemListHI65MFD)</SPAN>
            .PivotItems(varItemListHI65MFD(iHI65MFD)).Visible = True</SPAN>
        Next iHI65MFD</SPAN>
    End With</SPAN>
    objFieldHI65MFD.Orientation = xlRowField</SPAN>
    objFieldHI65MFD.Orientation = xlRowField</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "SILD").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "REVA").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "ADCR").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "LETA").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "TPAP").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "TRAC").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "TRAB").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "REVC").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "TOBI").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "PUL").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "ACTP").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "GMPX").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "FLBO").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "IVGF").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "GKSC").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "GKIV").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "GGSC").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "GGIV").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "IVGS").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "GAMS").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "HIZA").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "IVGP").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "GAMC").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "GLSA").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "ZEMA").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "ARAL").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "REMP").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "REMO").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "RMIV").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "RMIP").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "TYVS").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "TYVP").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "TYVA").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "VENT").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "VENP").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "FPAP").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "EPOP").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "EPAP").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "EPAC").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "F100").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "FL50").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "FZYR").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "BERT").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "ELAP").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "ALDZ").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "FABZ").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "FABF").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "NGLZ").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "NGLF").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "MYOZ").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "LUMZ").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "VPRV").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "ZYMF").Position = 1</SPAN>
    objFieldHI65MFD.PivotItems( _</SPAN>
        "ZYME").Position = 1</SPAN>
    Set objFieldHI65MFD = objTableHI65MFD.PivotFields("RX HOME ID #")</SPAN>
    objFieldHI65MFD.Orientation = xlRowField</SPAN>
    Set objFieldHI65MFD = objTableHI65MFD.PivotFields("RX HOME ID #")</SPAN>
    objFieldHI65MFD.Orientation = xlDataField</SPAN>
    objFieldHI65MFD.Function = xlCount</SPAN>
    objFieldHI65MFD.NumberFormat = "Number"</SPAN>
    objTableHI65MFD.RowAxisLayout xlOutlineRow</SPAN>
    objTableHI65MFD.TableStyle2 = "PivotStyleMedium11"</SPAN>
    objTableHI65MFD.Location = "'HI-MEDCO'!$R$1"</SPAN>
    Sheets("Sheet161").Select</SPAN>
    Sheets("Sheet161").Delete</SPAN>
    ActiveWorkbook.ShowPivotTableFieldList = False</SPAN>
    Sheets("HI-MEDCO").Select</SPAN>
    Columns("P:P").Select</SPAN>
    Selection.ColumnWidth = 1.1</SPAN>
    Columns("Q:Q").Select</SPAN>
    Selection.ColumnWidth = 1.1</SPAN>
    Cells.Select</SPAN>
    Selection.Rows.AutoFit</SPAN>
    Selection.Columns.AutoFit</SPAN>
    Range("A1").Select</SPAN>
    Sheets("HI-65-MDATA").Select</SPAN>
    ActiveWindow.SelectedSheets.Visible = False
</SPAN>
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
You must log in or register to reply here.

Similar threads

Cubist
  • Solved
VBA - Need help optimize code that's updating pivot tables.
Replies
1
Views
266
Cubist
Cubist
C
  • Solved
VBA Add Pivot Table Sheet Name
Replies
1
Views
166
Anthony47
A
M
  • Question Question
faster vba code for creating a pivot
Replies
5
Views
1K
Monicasinha
M
C
  • Question Question
Pivot Table VBA
Replies
0
Views
206
chaddres
C
S
  • Question Question
VBA Pivot Table
Replies
3
Views
2K
Domenic
D

Forum statistics

Threads
1,223,248
Messages
6,171,027
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top