troubleshooting Question

Excel 2003 VBA "Subscript out of range"

Avatar of glwilliams
glwilliamsFlag for United States of America asked on
Office Suites-Other
4 Comments1 Solution2005 ViewsLast Modified:
I am getting an error "Subscript out of range".  Don't understand it, because it is exactly the same in another workbook that works.

Answer should point out problem and explain what I did wrong.
' 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
Private Const cstrMod = "basCalc"
'Range of the HNWSF Products Sales Data
Private Const cstrHNWSFSales As String = "$A$4:$H$104"
Public Sub CreatePivot()
'On Error GoTo ErrHandler
    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
'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 = _
'Format table
    'Remove grand totals for columns and rows
    With ActiveSheet.PivotTables("PivotTable1")
        '.ColumnGrand = False
        .RowGrand = False
        .HasAutoFormat = True
    End With
    'Remove subtotals for customer
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Customer").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    'Format data numbers
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Sales")
        .NumberFormat = "#,##0"
    End With
    'Rename table
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Sales").Caption = _
        "Sales Totals"
    'Clean up display
    ActiveWorkbook.ShowPivotTableFieldList = False
    Application.CommandBars("PivotTable").Visible = False
End Sub
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros