VBA - Subscript out of range error

[reymon2012]

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

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

 

[mrshl9898]

Should i add this to the existing code or do i need to create another module. Sorry i'm still new to VBA.

new module

 

[JoeMo]

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.

 

[mrshl9898]

Huh, missed the "Personal" part. Good catch.

 

[jen ngu]

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

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

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"