Macro - Pivot Tables

[cldavis]

I am new to the world of macros and pivot tables and VBA. But I did manage to create a macro that creates 3 pivot tables on different worksheets. My problem is this: My Data is all there, but the pivot tables do not pick up the last line of data. So in one total is off on all pivot tables. How do I get the last line to pick up? Code when trouble shooting is
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Count"), "Count of Count", xlCount

What does the xlCount do?

Thanks,

Cheryl

[cldavis]

Additional Info: The R1C1:R1913C17 is my problem. When I created the Macro I only had 1913 lines of data - now I have 1914. How do I make the rows change to read all rows with data.

Thanks

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"DATA!R1C1:R1913C17", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion12
Sheets("Sheet1").Select

[Djblois]

sorry my mistake - look at the next post

[Djblois]

cldavis

This is the code that I use to create pivot Tables:

[VBA]Sub ptBegin()

Dim pTcache As PivotCache
Dim workingsheet as WorkSheet
Dim Wb(2) as WorkBook
Dim PT as PivotTable
Dim finalrow as long, finalColumn as long

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
FinalColumn = Cells(1, Columns.Count).End(xlToLeft).Column

Set WorkingSheet = Activesheet
WB(1).Names.Add Name:="pvtData", RefersToR1C1:=workingSheet.Cells(1, 1).Resize(FinalRow, FinalColumn)

Set pTcache = WB(1).PivotCaches.Add(SourceType:=xlDatabase, SourceData:="pvtData")
Set PT = pTcache.CreatePivotTable(TableDestination:=workingSheet.Range("A1"), TableName:="PivotTable")
PT.ManualUpdate = True
PT.RowGrand = False
PT.PrintTitles = True
PT.NullString = "0"
PT.DisplayErrorString = True
PT.HasAutoFormat = False
PT.PreserveFormatting = True
Set pTcache = Nothing

End Sub[/VBA]

[cldavis]

Thanks, I will give this a try. I also changed 1913 to 3000 and that seemed to work. I do not think the rows would every exceed 3000, so should work, but you never know when someone will add extra columns to the data.

So, if I were to begin from scratch, would I record the macro and then edit with this code at the beginning? or what?

Thanks

[Djblois]

This code is set to accept expanding rows automatically. If you look it refers to pvtData not a set range, so as the range increases it would expand automatically (once you hit refresh on the pivot Table. This code is just creating the table with no information yet. Then you still need to add the column, row, and data fields. If you really want to learn how to program Pivot Tables - I have created a full add-in to create pivotTables. I can send you a copy (it might be overwhelming at first). Last Year, I never thought I would get this far.

[cldavis]

That would be great, I can see several uses for pivot tables. Our users would love for their jobs to be made easier.

Thanks!

[Djblois]

If you send me a PM on here with you email, I will email you a copy of my latest version. Do not post your email here.