Struggling to find the right data source range in VBA pivot table

Question

New to VBA and I've been struggling to find the right data source for my Pivot table in Excel. The data source should be coming from column "B" in sheet "Monthly". Any tips on how to change data source or a different method for it?

This is what i have so far:

Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PvtCache As PivotCache
Dim PvtTable As PivotTable
Dim DR As Range
Dim LR As Long
Dim LC As Long
Dim sht5 As Variant

On Error GoTo errhandler

With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

For Each sht5 In ActiveWorkbook.Worksheets
If sht5.Name = "Pivot" Then
    sht5.Delete
End If
Next sht5

On Error GoTo 0
Worksheets.Add.Name = "Pivot"

    Set DSheet = Worksheets("Monthly")
    Set PSheet = Worksheets("Pivot")
    
        LR = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
        LC = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column

    Set DR = DSheet.Cells(1, 1).Resize(LR, LC) 'this line is what i can't figure out

    Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DR)
    Set PvtTable = PvtCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="Monthly")

With ActiveSheet.PivotTables("Monthly").PivotFields("Chutes")
    .Orientation = xlRowField
    .Position = 1
End With

With ActiveSheet.PivotTables("Monthly").PivotFields("Chutes")
    .Orientation = xlDataField
    .Position = 1
    .Function = xlCount
End With

Thanks!

After following the suggestion on fixing how you get LR, try using Set DR = DSheet.Range( DSheet.Range("B1"), DSheet.Cells(LR, LC) ) — Tim Williams
– Tim Williams, Commented May 8 at 23:40

Michal · Accepted Answer · 2025-05-08 23:24:13Z

1

Column B, is the second column, assuming the usual way of counting from A, so using number two to represent it is a much better idea than using number one to do so.


LR = DSheet.Cells(DSheet.Rows.Count, 2).End(xlUp).Row ' Column B is column 2
Set DR = DSheet.Range("B1:B" & LR)

answered May 8 at 23:24

Michal

7,6744 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Light3n May 9 at 0:13

Thanks! Makes sense. Only its bugging right now with my "Set PvtTable" and i can't figure out why. My table destination should be 1,1 for column A first row but its refusing to do so. Any ideas?

Tim Williams May 9 at 1:43

FYI "bugging out" is not a very useful description of the problem you're having. Are you getting an error message? If Yes, what is it exactly?

Collectives™ on Stack Overflow

Struggling to find the right data source range in VBA pivot table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related