Copying and pasting data from a "filtered" Pivot Table

Question

I have tried to search all the previous questions regarding this topic but I'm still unclear on if there is an easier solutions to this.

Situation: I have a worksheet that has a pivot table that extracts data from a master data pull I do in the mornings. The pivot table it self has 1 Filter selection for the month and 2 Row filters for type and supplier. The two columns are 1) For the type and for each type there are different suppliers listed 2) Volume.

Every morning from the pivot table I copy all the suppliers and there associated volume for each "type" and paste it into a separate balancing sheet (paste location does not change but sometimes need to add additional cell rows to if the # of suppliers has increased). There's about 40 different "type" options so it is an extremally manual task which I am trying to automate. The number of suppliers(rows) change constantly so the range is dynamic hence a simple macro doesn't work.

From what I've read I see two options on how to do this:

1. Use a code to copy the entire pivot table and paste it into another sheet and use a code to search for each "type" and then copy the range of suppliers and associated volume listed under each type.

2. Use a code to update the pivot table filter for each "type" and on each selection have function to copy/paste the range of suppliers and associated volume.

Looking for advice on which method is the best or if anyone has a different solution to this.

Answer 1

So, as I understand it, you want to create a separate worksheet for each data column (type) in your pivot table, paste all the row descriptions in, paste the column values in and move on to the next worksheet. By using the Pivot table object in VBA you can accomplish this without filters.

First declare a variable 'pt' Dim pt as pivotTable, and Set pt = WorkSheets("mywks").PivotTables("pivotname"), where mywks is the name of the worksheet containing the pivot table and pivotname is the name Excel gave to the pivot table ( in case you have more than one in the sheet).

Now you can work with a set of range variables to access the relevant parts of the Pivot table. For example, pt.TableRange1 is a range object that includes the row & column headers and all the data, pt.columnRange is a range that includes just the column headers, and pt.RowRange takes in all the row headers (not including the labels down the side).

You can loop across the pivot table columns one at a time with a VBA statement like For Each col in mywks.range(Cells(pt.TableRange1.Rows(1).row,pt.ColumnRange.Columns(1).column),Cells(pt.TableRange1.rows(pt.TableRange1.rows.count).row,pt.ColumnRange.Columns(pt.ColumnRange.Columns.count).column)).Columns , you can describe the row labels with a similar range set rowlabels = mywks.range(Cells(pt.TableRange1.Rows(1).row,pt.RowRange.Columns(1).column),Cells(pt.TableRange1.Rows(pt.TableRange1.Rows.Count).row, pt.RowRange.Columns(pt.RowRange.Columns.count).column)) and create a new worksheet with Set newwks=Worksheets.Add.

Once you've set up these ranges it's pretty easy to copy in the rowlabels range and the col range into the new worksheet. You can set an arbitrary row and column in the destination worksheet like this: Set destlabels = newwks("A3").resize(rowlabels.rows.count,rowlabels.columns.count); and then Set destdata = destlabels.offset(0,rowlabels.columns.count).resize(col.rows.count,col.columns.count).

Now with all that setup you copy the data to the new worksheet with just destlabels = rowlabels and destdata = col.

Finally, make sure you save the spreadsheet under a different name. Don't overwrite the base worksheet. It's easier to create the new worksheets every day than it is to resize them all the time.