VBA - Subscript out of range error

reymon2012

New Member
Joined
Feb 3, 2012
Messages
17
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Try this.

If still out of range the sheet does not exist in the book the code is in.

VBA Code:
Dim wb As Workbook
Dim wsData As Worksheet

Set wb = ThisWorkbook
Set wsData = wb.Sheets("Prompted Tasks Grid")
 
Upvote 0
If you are getting that error at this Line:

Set wsData = ThisWorkbook.Worksheets("Prompted Tasks Grid")

the likely reason is that the workbook your code is in has no worksheet with the tab name "Prompted Tasks Grid".
If you think that sheet is present, check the spelling and ensure there are no unintentional extra spaces in it.
 
Upvote 0
Try this.

If still out of range the sheet does not exist in the book the code is in.

VBA Code:
Dim wb As Workbook
Dim wsData As Worksheet

Set wb = ThisWorkbook
Set wsData = wb.Sheets("Prompted Tasks Grid")

Still getting the Run-time error '9':
Subscript out of range.

The sheet name is exactly "Prompted Task Grid". :(
 
Upvote 0
If you are getting that error at this Line:

Set wsData = ThisWorkbook.Worksheets("Prompted Tasks Grid")

the likely reason is that the workbook your code is in has no worksheet with the tab name "Prompted Tasks Grid".
If you think that sheet is present, check the spelling and ensure there are no unintentional extra spaces in it.

Same error and sheet name is exactly "Prompted Tasks Grid".
At this point don't know what to do..:(
 
Upvote 0
Same error and sheet name is exactly "Prompted Tasks Grid".
At this point don't know what to do..:(
In post #4 you said the sheet name is exactly "Prompted Task Grid" which is not what you replied in post #5. Also, can you confirm the sheet in question is in the same workbook as the code that producing the error?
 
Upvote 0
See what the results are here:

VBA Code:
Sub ListSheetsName()
Dim s, i
ReDim s(1 To Sheets.Count)
For i = 1 To Sheets.Count
s(i) = ThisWorkbook.Sheets(i).Name
Debug.Print s(i)
Next
End Sub
 
Upvote 0
In post #4 you said the sheet name is exactly "Prompted Task Grid" which is not what you replied in post #5. Also, can you confirm the sheet in question is in the same workbook as the code that producing the error?

Apologies for the typo. Sheet name is "Prompted Tasks Grid", and also i saved the code on my personal workbook.
 
Upvote 0
See what the results are here:

VBA Code:
Sub ListSheetsName()
Dim s, i
ReDim s(1 To Sheets.Count)
For i = 1 To Sheets.Count
s(i) = ThisWorkbook.Sheets(i).Name
Debug.Print s(i)
Next
End Sub

Should i add this to the existing code or do i need to create another module. Sorry i'm still new to VBA.
 
Upvote 0
Check the sheet name for leading / trailling OR double spaces within the name
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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