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:
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 :)
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.
Mackers691 1 year ago
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.Create(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
Gabsacuarius 1 year ago
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 :) *
LearnAccessByCrystal 1 year ago