Need help with a PivotTable Macro
Posted by Ed on February 14, 2002 2:59 PM
I have a macro that imports CSV data into a workbook then cuts and paste the data to separate worksheets based on changing dates in column b, all of this works (thanks to Barrie Davidson's help), each sheet always contain the same number of columns and column headings, but the number of rows varies from sheet to sheet. I would like to add additional logic to the macro that would generate, on each sheet, a PivotTable. I can record the pivottable macro and incorporate it into the existing macro, but I am not able to figure out how to edit the code so that it will dynamically generate a pivottable on each sheet in the workbook, with number of rows varying from sheet to sheet. Any help would be appreciated.
Posted by Ed Acosta on February 14, 2002 3:58 PM
After you record the macro you should see the line of code that identifies your range selected. Should look somethin like this:
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R6C2", TableDestination:="R10C7", TableName:="PivotTable2"
Before this section add this line of code:
RW = Range("A65536").End(xlUp).Row
This counts the number of rows you have. Then edit the range code like this:
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R" & RW & "C2", TableDestination:="R10C7", TableName:="PivotTable2"
You should now have dynamic pivot tables.
Posted by Ed on February 14, 2002 4:13 PM
What about code for sheet selection?
Thanks for the code, looks like it will address the varying number of rows, but it appears to only address "sheet1"....my workbook will contain multiple sheets. I need a pivot table on each sheet in the workbook. The pivot table for each sheet will be the same, just based on different data values and varying rows of information.
Posted by Mark W. on February 15, 2002 8:37 AM
Re: What about code for sheet selection?
Why are you segregating your data in the first
place? What version of Excel are you using?
Posted by Ed on February 15, 2002 12:01 PM
Re: What about code for sheet selection?
Mark,
Through trial and error I have figured out the logic I need to automatically generate the pivot tables.
The workbook contains 12 sheets, one for each month. Each sheet contains 30 columns of billing data that I want segrerated into a pivot table on each sheet. I have the macro working.
Thanks for asking.
