VBA to filter data from pivot table and move to new docs

**GregStewartPTC** · 02-19-2018, 04:20 PM

I was trying to write a macro to create multiple documents based on filtered criteria from a pivot table I am sent daily, but it became clear that VBA was probably a much better solution as I kept getting 1004 errors 'unable to set the visible property of the pivotitem class'. I need help with a code that can filter Column I by employee name, then Column D by city name, and then copy everything from Columns A thru Q and paste to a new excel document, 1 document for each employee listed by name, with tabs for each city. Is this possible?

Because this is being extracted from a pivot table, and it contains financial info, I'm unable to upload an example spreadsheet. I have uploaded a picture to give you an idea of what I am working with, with redacted names.

Thank you in advance for any and all help you can provide.

**dflak** · 02-21-2018, 12:19 PM

I don't think you'll get takers without a sample workbook. Is there any way you can "sanitize" the data? If you have direct access to the source data, you can modify it.

However, depending on how the spreadsheet is set up, you can recover the source data from it. If there is not a grand total of everything cell in the spreadsheet, set one up. Then double click this. Double clicking a cell in a spreadsheet recovers the source data that went into the calculation of that cell. In case of the grand total of everything, that's all the source data.

I recommend paring the data down so you have enough to be representative of what you are doing. We don't need 100,000 rows of data when 100 will be good enough to illustrate the point.

Then you can use CTRL-H (Find and Replace) to change things like client names and company names and RANDBEWEEN (x,y) for monetary values (copy the column and paste back as values only).

Then switch the data source of the spreadsheet to this modified table.

Remove all original data and you should be safe.

**GregStewartPTC** · 02-21-2018, 12:20 PM

Hi dflak. The way the information was being sent to me originally wasn't very conducive to creating the workbooks that were requested of me. I closed this thread and started one from scratch with data that I could actually upload as an example - and a little clearer instruction on what I'm trying to accomplish. Thank you for the reply!

VBA to filter data from pivot table and move to new docs

LinkBack

Thread Tools

Rate This Thread

Display

VBA to filter data from pivot table and move to new docs

Re: VBA to filter data from pivot table and move to new docs

Re: VBA to filter data from pivot table and move to new docs

Thread Information

Users Browsing this Thread

Similar Threads

Pivot table filter not changing data in table - Using macro

writing a macro to move data from a pivot table to individual workbooks sorting by filter

[SOLVED] Select Automatic Report Filter in Pivot where Filter exist in Data table else select Blank

Sorting times from a pivot table being pulled from an external source...? [google docs]

Using formulas to move data from one worksheet to another from a Pivot Table

Filter data set within pivot table

Data filter in Pivot table

Bookmarks

Bookmarks

Posting Permissions