VBA - Subscript out of range error

reymon2012

New Member
Joined
Feb 3, 2012
Messages
12
Still learning VBA and code below is for creating Pivot Table. However i'm getting Subscript out of Range Error for line item 6. Highlighted in yellow below.
i'm pulling the report from database and the source data worksheet name is " Prompted Tasks Grid" and the destination worksheet name is " Pivot Table".
Any help is sincerely appreciated


VBA Code:
Sub Create_Pivot_Table()
Dim LastRow As Long, LastColumn As Long
Dim DataRange As Range
Dim PTCache As PivotCache
Dim PT As PivotTable

Set wb = ThisWorkbook
[COLOR=rgb(250, 197, 28)][B]Set wsData = ThisWorkbook.Worksheets("Prompted Tasks Grid")[/B][/COLOR]

With wsData
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
LastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column

Set DataRange = .Range(.Cells(1, 1), .Cells(LastRow, LastColumn))

Set wsPT = wb.Worksheets.Add
wsPT.Name = "Pivot Table"

Set PTCache = wb.PivotCaches.Create(xlDatabase, DataRange)

Set PT = PTCache.CreatePivotTable(wsPT.Range("B5"), "Pt_CADBTask")
With PT

    '//Pivot Table Layout Settings
    .RowAxisLayout xlTabularRow
    .ColumnGrand = True
    .RowGrand = False
    
    .TableStyle2 = "PivotStyleMedium9"
    .HasAutoFormat = False
    .SubtotalLocation xlAtBottom
    
    '//Row Section (layer1)
    With .PivotFields("Assigned To Name")
    .Orientation = xlRowField
    .Position = 1
    .LayoutBlankLine = False
    
    .Subtotals(1) = True
    .LayoutForm = xlTabular 'xloutline
    .LayoutCompactRow = True
    End With
    
    '// Values Section
    With .PivotFields("Loan #")
    .Orientation = xlDataField
    .Position = 1
    .Function = xlCount
    .NumberFormat = "#,##;(#,##);-"
    .Caption = "Quanty"
    End With
    
wsPT.Cells.EntireColumn.AutoFit
  
End With

End With

End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,434
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Apologies for the typo. Sheet name is "Prompted Tasks Grid", and also i saved the code on my personal workbook.
If the code is in your Personal workbook and you run it from there on another workbook, then "Prompted Tasks Grid" is likely NOT in Thisworkbook (i.e. the Personal.xlsb workbook).
Try changing this line:
Set wsData = ThisWorkbook.Worksheets("Prompted Tasks Grid")
to this:
Set wsData = ActiveWorkbook.Worksheets("Prompted Tasks Grid")

You might need to make some other changes as well, I haven't looked at all your code but, for now, let's see if this change fixes the subscript out of range error.
 

jen ngu

New Member
Joined
Mar 15, 2021
Messages
1
Office Version
  1. 2010
Platform
  1. Windows
Still learning VBA and code below is for creating Pivot Table. However i'm getting Subscript out of Range Error for line item 6. Highlighted in yellow below.
i'm pulling the report from database and the source data worksheet name is " Prompted Tasks Grid" and the destination worksheet name is " Pivot Table".
Any help is sincerely appreciated


VBA Code:
Sub Create_Pivot_Table()
Dim LastRow As Long, LastColumn As Long
Dim DataRange As Range
Dim PTCache As PivotCache
Dim PT As PivotTable

Set wb = ThisWorkbook
[COLOR=rgb(250, 197, 28)][B]Set wsData = ThisWorkbook.Worksheets("Prompted Tasks Grid")[/B][/COLOR]

With wsData
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
LastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column

Set DataRange = .Range(.Cells(1, 1), .Cells(LastRow, LastColumn))

Set wsPT = wb.Worksheets.Add
wsPT.Name = "Pivot Table"

Set PTCache = wb.PivotCaches.Create(xlDatabase, DataRange)

Set PT = PTCache.CreatePivotTable(wsPT.Range("B5"), "Pt_CADBTask")
With PT

    '//Pivot Table Layout Settings
    .RowAxisLayout xlTabularRow
    .ColumnGrand = True
    .RowGrand = False
  
    .TableStyle2 = "PivotStyleMedium9"
    .HasAutoFormat = False
    .SubtotalLocation xlAtBottom
  
    '//Row Section (layer1)
    With .PivotFields("Assigned To Name")
    .Orientation = xlRowField
    .Position = 1
    .LayoutBlankLine = False
  
    .Subtotals(1) = True
    .LayoutForm = xlTabular 'xloutline
    .LayoutCompactRow = True
    End With
  
    '// Values Section
    With .PivotFields("Loan #")
    .Orientation = xlDataField
    .Position = 1
    .Function = xlCount
    .NumberFormat = "#,##;(#,##);-"
    .Caption = "Quanty"
    End With
  
wsPT.Cells.EntireColumn.AutoFit

End With

End With

End Sub
Reason is the worksheet name has to be named as Data, by default this sheet name could be Sheet1 or any other name, rename it to Data
Still learning VBA and code below is for creating Pivot Table. However i'm getting Subscript out of Range Error for line item 6. Highlighted in yellow below.
i'm pulling the report from database and the source data worksheet name is " Prompted Tasks Grid" and the destination worksheet name is " Pivot Table".
Any help is sincerely appreciated


VBA Code:
Sub Create_Pivot_Table()
Dim LastRow As Long, LastColumn As Long
Dim DataRange As Range
Dim PTCache As PivotCache
Dim PT As PivotTable

Set wb = ThisWorkbook
[COLOR=rgb(250, 197, 28)][B]Set wsData = ThisWorkbook.Worksheets("Prompted Tasks Grid")[/B][/COLOR]

With wsData
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
LastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column

Set DataRange = .Range(.Cells(1, 1), .Cells(LastRow, LastColumn))

Set wsPT = wb.Worksheets.Add
wsPT.Name = "Pivot Table"

Set PTCache = wb.PivotCaches.Create(xlDatabase, DataRange)

Set PT = PTCache.CreatePivotTable(wsPT.Range("B5"), "Pt_CADBTask")
With PT

    '//Pivot Table Layout Settings
    .RowAxisLayout xlTabularRow
    .ColumnGrand = True
    .RowGrand = False
   
    .TableStyle2 = "PivotStyleMedium9"
    .HasAutoFormat = False
    .SubtotalLocation xlAtBottom
   
    '//Row Section (layer1)
    With .PivotFields("Assigned To Name")
    .Orientation = xlRowField
    .Position = 1
    .LayoutBlankLine = False
   
    .Subtotals(1) = True
    .LayoutForm = xlTabular 'xloutline
    .LayoutCompactRow = True
    End With
   
    '// Values Section
    With .PivotFields("Loan #")
    .Orientation = xlDataField
    .Position = 1
    .Function = xlCount
    .NumberFormat = "#,##;(#,##);-"
    .Caption = "Quanty"
    End With
   
wsPT.Cells.EntireColumn.AutoFit
 
End With

End With

End Sub
Yr sheet name seems pretty long, try copy the sheet name & paste onto the vba module or try out using a simple name like "data"
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,129
Messages
5,640,286
Members
417,135
Latest member
zeusmining

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
Top