Pivot Table Via VBA

Question

I'm trying to be able to use this macro to create this pivot table each day on the same worksheet name. The only thing that will change is the datasource row as I never know how many rows it will be. I tried to set the entire string into the CurSourceData variable and then just use that but it fails and says invalid arg.

Any ideas would be helpful.

Sheets("Cases 23+ Day (Due Today)").Select Range("A1").Select Selection.End(xlDown).Select Selection.End(xlDown).Select CurRow = ActiveCell.Row CurSourceData = "Cases 23+ Day (Due today)!R1C2:R" & CurRow & "C20" ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ CurSourceData, Version:=xlPivotTableVersion14). _ CreatePivotTable TableDestination:="Master Summary!R3C7", TableName:= _ "PivotTable3", DefaultVersion:=xlPivotTableVersion14 Sheets("Master Summary").Select Cells(3, 7).Select ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _ "PivotTable3").PivotFields("Status"), "Count of Status", xlCount With ActiveSheet.PivotTables("PivotTable3").PivotFields("Status") .Orientation = xlRowField .Position = 1 End With

grahamj42 · Accepted Answer · 2013-03-19 20:07:28Z

The SourceData argument of PivotCaches.Create is either a Range or a Connection. I think there's also a problem with the space in your sheet name and the answer is to use single quotes. Try :

Set CurSourceData = Worksheets("Cases 23+ Day (Due today)").Range("B1", "T" & CurRow)

(I detest R1C1 notation and couldn't work out how to use it here.)

I appreciate the reply. I did try changing that and that did not help. Any other ideas? In addtion, if I actually use the correct correct information with "" as needed it errors out on slecting master summary too??

T_D · Accepted Answer · 2014-09-09 16:16:21Z

This has worked for me, assuming your data set starts in cell A1:

Dim MyRange As Range Dim lrow As Long Dim lcol As Long lrow = ActiveSheet.Cells(Application.Rows.Count, 1).End(xlUp).Row lcol = ActiveSheet.Cells(1, Application.Columns.Count).End(xlToLeft).Column Set MyRange = ActiveSheet.Cells(1, 1).Resize(lrow, lcol)

Good luck!

Collectives™ on Stack Overflow

Pivot Table Via VBA

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related