I am trying to write some VBA to loop through a PivotTable, and I am struggling with the appropriate objects and names.
My PivotTable has a handful of filters (the code needs to be able to accomodate a changing number here), 7 row fields (this will be unchanging), and one column field (the one field will be unchanging, but the number of items contained in that column in the dataset will change and so the code needs to be able to dynamically adjust for the table taking up more or less total columns).
What I want to do is loop through the table: starting with the first row of row field data, go through the other row fields and populate some variables with row-specific information. Then, loop through the data columns for that row and call a subroutine with the value in each cell. Repeat the process through each row of the table.
In the sample table below: the first row would be the row starting with Value A/Value B. The code would populate the variables as appropriate for the row fields, then would start looping through the column fields. In this caes, there are two columns and so it would call a subroutine twice, first with "101" as the parameter and second with "201". Then, the next row would be Value A/Value C, with the inner loop calling the subroutine with the column values of "245" and "122."
Bonus: I'd also like to be able to start and end from a specified row of the sheet. So, if the first row of table content (not headers) is row 6 on the sheet, I could tell the VBA to start at (sheet) row 8 and it would be able to figure out that I need the third row of table data.
My PivotTable has a handful of filters (the code needs to be able to accomodate a changing number here), 7 row fields (this will be unchanging), and one column field (the one field will be unchanging, but the number of items contained in that column in the dataset will change and so the code needs to be able to dynamically adjust for the table taking up more or less total columns).
What I want to do is loop through the table: starting with the first row of row field data, go through the other row fields and populate some variables with row-specific information. Then, loop through the data columns for that row and call a subroutine with the value in each cell. Repeat the process through each row of the table.
In the sample table below: the first row would be the row starting with Value A/Value B. The code would populate the variables as appropriate for the row fields, then would start looping through the column fields. In this caes, there are two columns and so it would call a subroutine twice, first with "101" as the parameter and second with "201". Then, the next row would be Value A/Value C, with the inner loop calling the subroutine with the column values of "245" and "122."
| Filter 1 name | Filter 1 dropdown | |||
| Filter 2 name | Filter 2 dropdown | |||
| Value field name | Column field title | |||
| Row field 1 name | Row field 2 name | (...rest of field rows) | Column field 1 | Column field 2 |
| Value A | Value B | ... | 101 | 201 |
| Value A | Value C | ... | 245 | 122 |
Bonus: I'd also like to be able to start and end from a specified row of the sheet. So, if the first row of table content (not headers) is row 6 on the sheet, I could tell the VBA to start at (sheet) row 8 and it would be able to figure out that I need the third row of table data.
