Create regular table from pivot table w/ filters applied

Perhaps the following will help

https://peltiertech.com/referencing-...ranges-in-vba/

If not, then maybe you can define a named dynamic range to overlay the pivot table data you wish to copy. Then copy and paste it into the table.

Here is some VBA code to clear the table of its old contents.

Please Login or Register  to view this content.

To clear a table use the following syntax:

Please Login or Register  to view this content.

Ok I've downloaded some practice data set online because I cannot share real data, so it does not make any sense the way it is, but I hope the concept remains the same and what I would like. Sheet pivot is what I would like.
Each "fresh" Workbook I get has just the raw data, and I have to manually create the pivot table, apply the filters, copy & paste the data to another sheet.

Here you go. There was a slight curve. The column headers can vary in both name and number.

The key to this code is the definition of a pair of named dynamic ranges

Pivot_Copy =OFFSET(Pivot!$A$5,0,0,COUNTA(Pivot!$A:$A)-3,COUNTA(Pivot!$4:$4))
Pivot_Headers =OFFSET(Pivot!$A$4,0,0,1,COUNTA(Pivot!$4:$4))

The offset command has 5 arguments: a starting cell, how many cells to go down, how many rows to go right and the number of rows and columns to return.

So =OFFSET(Pivot!$A$5,0,0,COUNTA(Pivot!$A:$A)-3,COUNTA(Pivot!$4:$4)) means
Start in A5
Go down zero rows
Go right zero columns
Return COUNTA(A:A)-3 rows (-3 because we don't want to count headers and the Grand Total row)
Return COUNTA(Pivot!$4:$4) columns

The VB code removes all columns but the first and then clears the table and then copy in the column headers and the body of the pivot table.

Please Login or Register  to view this content.

Ok, thanks that is great. However, just for the protocol: This is a task that requires vba, it is not some trivial thing which is part of the pivot-table- features?

The file in which this task has to be done will be a new file every day, so.
1. the macro would have to be saved in the personal macro workbook
2. the sheet "desired table" will not be present
3. the table that is referenced,

HTML Code: 

nis not present.

I modified the code to create a sheet with the name "Desired Table" and create a table, "Table1".

Please Login or Register  to view this content.

EDIT:

Also it is not important to clear the table, I addese these lines at the beginning of the SUBROUTINE

Please Login or Register  to view this content.

that way the whole sheet gets deleted, a new one with a new table1 gets created

As a Supply Chain Analyst, I generate about a dozen reports a day. This is the system I use. Maybe you can adapt it for your use.

First, I established a directory where I download all of my data. Then as I do my downloads, I save them with the same name every time. For example, the data directory is \\Server\Reports\Data. In my case, this is a shared directory so other people can take over my work in my absence. So, if I am downloading Purchase Order information, I save it in a file Open PO.xlsx. I overwrite this file every day. I do not keep historical copies. I do have a way to keep snapshots if I need them.

Then I open an Excel File that I want to turn into a report.

I use Data > Get Data > From File > From Workbook to import the data onto a sheet. Navigate to the data workbook (I recommend using pathnames instead of mapped drive names) and select it. This will bring up the Power Query editor. It is quite intuitive and you can select columns and do filtering to narrow down the data.

The data is imported into a new sheet. Then go to Data > Queries and Connections and select the new connection. Right click on the connection name and select properties. A dialog box ap pears with some options. Click OFF the one that allows background queries. At the top of the dialog box give the query a new name such as Table_Open_PO. This not only changes the name of the query, but it also renames the Table containing the downloaded data.

I also recommend renaming the sheet to something meaningful.

Then invoke the macro recorder and click on the query and click refresh. Turn off the macro recorder. Rename the macro Refresh Data.

Now build the pivot table. Since the pivot table is built off an Excel Table, it automatically adjusts for the number of rows imported.

For your case, create the named dynamic ranges. They automatically adjust to the number of rows and columns returned by the pivot table.

Then copy in the code.

You can make some adjustments to the code. To the Refresh Data macro add this below the refresh line:

Please Login or Register  to view this content.

Now when you refresh the data, the entire report is run and is ready to go.

With this system, there is no need to create new files and copy the code in.