Dynamic Range Pivot

Hi,

I am new to VBA.

I have a data set from which a pivot table is created. A duplicate filter is applied on the data and then I need to copy the data to another sheet. How do I make dynamic selections? I have highlighted where I believe the data needs to be dynamic.

Please Login or Register  to view this content.

Hi fireboltpk,

Could you please edit your question by selecting all the code and clicking on the "#" icon above the message box. This will make the code much more readable.

After you do that the answer to your question lies in Dynamic Named Ranges. You create them using the Formulas Tab => Named Manager. After you create the Dynamic Named Range you would change the line of:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

http://www.contextures.com/xlNames01.html

If you import or copy / paste your source data into an Excel Table and then use the table as the Pivot Table source, you will not need code at all. As you add or delete information to the Excel Table, the pivot table will adjust automatically to the new range. Excel tables also "remember" and copy down formulas, validations, formats, etc.

Here is some information on Excel Tables: http://www.utteraccess.com/wiki/Tables_in_Excel.