Pivot Macro - Episode 1211

Eric writes in with a frustrating question; "Why can't the macro recorder record the action of creating a pivot table? No matter what I do, it hardcodes that the original pivot data is 469 rows tall." Eric doesn't want to learn VBA, but needs enough to be able to generalize the recorded code. Episode 1211 shows you a solution.

Category:

Howto & Style

Tags:

• Excel
• business
• accounting
• spreadsheets
• tutorial
• technology
• MrExcel
• macro
• VBA
• FinalRow
• PivotTable

[Mackers691]

Hello Bill

Very useful video. Using your suggestion in Excel 2003 (VB 6.5), I find you have to declare "finalrow" as Long. Also, why does this function not work for finding the max number of columns (which surely you may also need), e g:

finalcol = Cells(Columns.Count, 1).End(xlUp).Column

I always get a 1 when it's actually 4 in my example

There are a couple of alternatives:

1) simply remove the range reference;

2) use the lbound and ubound functions to determine the array size.

[Gabsacuarius]

Hi Bill!

I tryed to do this type of macro, but when I try to run it it send´s me an error: Invalid procedure call or argument

ActiveWorkbook.PivotCaches.Cre­ate(SourceType:=xlDatabase, SourceData:= _ DataSheet & "R1C1:R" & FinalRow & "C17", Version:=xlPivotTableVersion10­).CreatePivotTable _ TableDestination:=NewSheet & "R3C1", TableName:="PivotTable2", DefaultVersion _ :=xlPivotTableVersion10

And I don´t know how to fix it. Can you help me?

Thanks

[LearnAccessByCrystal]

Hi Bill,

Great video! Nice approach too ("I am not going to teach you VBA, I am just going to show you how to fix the macro recorder" ...smile). The best first step in using VBA (even for Access) is modifying what the Excel macro recorder does. I will be passing this video link on to others who are trying to learn, thanks :)

Warm Regards,

Crystal * (: have an awesome day :) *