VBA - Subscript out of range error

reymon2012

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

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,708
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")
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,413
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

reymon2012

New Member
Joined
Feb 3, 2012
Messages
9
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". :(
 

reymon2012

New Member
Joined
Feb 3, 2012
Messages
9

ADVERTISEMENT

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..:(
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,413
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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?
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,708

ADVERTISEMENT

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
 

reymon2012

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

reymon2012

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,028
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Check the sheet name for leading / trailling OR double spaces within the name
 

Watch MrExcel Video

Forum statistics

Threads
1,127,199
Messages
5,623,323
Members
415,966
Latest member
ctorohuamanchumo

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