Excel 2003 VBA "Subscript out of range"

' CreatePivot Macro
' Created: 12/16/2008 by Greg Williams
' Revised:
' Purpose: The purpose is to create and record sales projections in a table.
'          Using these projections, create a pivot table to make viewing the
'          data more user friendly.
'          Then create a database (named range) of the sales data so it can
'          be summed based on a variety of criteria, mostly the period.
'          The next step is to create dynamic criteria based on the data
'          recorded in the sales data table.
'          The last step is to create dsum() formulas to sum the data according
'          to the criteria.
'          Note: the data input must be a dynamic range.
 
'-----------------------------------------------------------------------------
Option Explicit
Option Compare Text
 
'DEFINE CONSTANTS
Private Const cstrMod = "basCalc"
 
'Range of the HNWSF Products Sales Data
Private Const cstrHNWSFSales As String = "$A$4:$H$104"
 
Public Sub CreatePivot()
 
'ERROR HANDLING
'On Error GoTo ErrHandler
 
'DEFINE VARIABLES
    Dim wshHNWSFSales As Worksheet
    Dim wshMarinadeSales As Worksheet
    Dim wshCustomSales As Worksheet
    
'Screen updating off
    Application.ScreenUpdating = False
 
'Get Sheets
    Set wshHNWSFSales = ThisWorkbook.Sheets("HNWSF_Sales") 'THIS IS WHERE I GET THE ERROR
    Set wshMarinadeSales = ThisWorkbook.Sheets("Marinade_Sales")
    Set wshCustomSales = ThisWorkbook.Sheets("Custom_Sales")
    
'Remove old pivot table
    wshHNWSFSales.Range("J1").CurrentRegion.Clear
    
'Need to clean out any empty rows in the HNWSF Sales Data Table
 
    
'Create the pivot table - the source date is a dynamic range
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    Range("A4").CurrentRegion.Address).CreatePivotTable TableDestination:= _
    ActiveSheet.Cells(1, 10), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
    
'Add fields
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Customer", _
        "Product"), ColumnFields:="Year"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales").Orientation = _
        xlDataField
    
'Format table
    'Remove grand totals for columns and rows
    Range("J1").Select
    With ActiveSheet.PivotTables("PivotTable1")
        '.ColumnGrand = False
        .RowGrand = False
        .HasAutoFormat = True
    End With
    
    'Remove subtotals for customer
    Range("J1").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Customer").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    
    'Format data numbers
    Range("J1").Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Sales")
        .NumberFormat = "#,##0"
    End With
    
    'Rename table
    Range("J1").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Sales").Caption = _
        "Sales Totals"
   
    'Clean up display
    ActiveWorkbook.ShowPivotTableFieldList = False
    Application.CommandBars("PivotTable").Visible = False
    Range("A1").Select
        
End Sub

Select allOpen in new window