Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros and pivot tables... help please!
hi all, i'm in the process of making a macro that will automatically create a pivot table for me each month. the problem is that the data i work with each month changes; that is, number of rows of data used for the pivot table is not always the same. for example, this month i could have 754 rows and 766 rows next month. i tried using a variable instead of an exact cell number, but excel seems to be really picky and wants an specific number instead. is it possible to create a macro for making pivot tables, even if the number of rows varies on a monthly basis? here are a few lines from the macro, if it helps: Range("K2").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "BFCCL!R1C1:R754C9").CreatePivotTable TableDestination:=Range("K2"), _ TableName:="PivotTable2" - the worksheet used ("BFCCL"), the starting cell ("R1C1") and the last column number ("C9") all remain the same per month. - only the row number ("R754") changes. i was wondering if anybody out there could help me out with this problem, as i'm totally stumped. i'd really, really appreciate it. thanks in advance, -fred -- white_rhino ------------------------------------------------------------------------ white_rhino's Profile: http://www.excelforum.com/member.php...o&userid=14552 View this thread: http://www.excelforum.com/showthread...hreadid=261886 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros and pivot tables... help please!
white_rhino wrote in message ...
hi all, i'm in the process of making a macro that will automatically create a pivot table for me each month. the problem is that the data i work with each month changes; that is, number of rows of data used for the pivot table is not always the same. for example, this month i could have 754 rows and 766 rows next month. i tried using a variable instead of an exact cell number, but excel seems to be really picky and wants an specific number instead. is it possible to create a macro for making pivot tables, even if the number of rows varies on a monthly basis? here are a few lines from the macro, if it helps: Range("K2").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "BFCCL!R1C1:R754C9").CreatePivotTable TableDestination:=Range("K2"), _ TableName:="PivotTable2" - the worksheet used ("BFCCL"), the starting cell ("R1C1") and the last column number ("C9") all remain the same per month. - only the row number ("R754") changes. i was wondering if anybody out there could help me out with this problem, as i'm totally stumped. i'd really, really appreciate it. thanks in advance, -fred Fred, Why don't you use a greater database ? For instance ("R1000"). The empty rows you can hide them in the pivot table. Is this maybey a solution for you ? Robert |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros and pivot tables... help please!
white_rhino wrote:
hi all, i'm in the process of making a macro that will automatically create a pivot table for me each month. the problem is that the data i work with each month changes; that is, number of rows of data used for the pivot table is not always the same. for example, this month i could have 754 rows and 766 rows next month. i tried using a variable instead of an exact cell number, but excel seems to be really picky and wants an specific number instead. is it possible to create a macro for making pivot tables, even if the number of rows varies on a monthly basis? here are a few lines from the macro, if it helps: Range("K2").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "BFCCL!R1C1:R754C9").CreatePivotTable TableDestination:=Range("K2"), _ TableName:="PivotTable2" - the worksheet used ("BFCCL"), the starting cell ("R1C1") and the last column number ("C9") all remain the same per month. - only the row number ("R754") changes. i was wondering if anybody out there could help me out with this problem, as i'm totally stumped. i'd really, really appreciate it. thanks in advance, -fred Fred, The best way to deal with this is to base the pivot table on a named range. Each month you can resize the named range to accommodate the extra rows. The size of the range can be changed using VBA or a better way is to use a dynamic named range. To do this use the offset function in the Refers to: box in the Define Name dialogue box. The following formula creates a named range that has A3 as the top left cell. It uses the COUNTA() function to count the number of non-empty cells in column A and the number of non-empty cells in row 3. It therefore automatically adjusts the range size to the number of rows and columns in the data. =OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A$3:$A$6553 6),COUNTA(Sheet1!$A$3:$IU$3)) All you need to do each month is: Delete the existing data from the named range. Copy in the new data. Refresh the pivot table. The pivot table will automatically pick-up the new data range. The above 3 steps can be done using a few lines of VBA. If you have a large data set, once the pivot table has been refreshed you can delete the data from the data range (assuming that you've checked the Save data with table layout option the PivotTable Options dialogue. The pivot table only refers to the original data range when you issue the refresh command. If you have other pivot tables that will use the same data, base them on the first pivot table and they will be updated at the same time. This will reduce the size of your file. Good luck cheers peterDavey Melbourne Australia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Macros for pivot tables | Excel Discussion (Misc queries) | |||
Macros/Protection/Pivot Tables...HELP | Excel Discussion (Misc queries) | |||
Pivot tables and macros | Excel Discussion (Misc queries) | |||
What's the best way to learn Pivot Tables and using Macros in Exc. | New Users to Excel | |||
Manipulating Pivot Tables With Macros | Excel Programming |