Using VBA to dynamically use filters

  • I have the attached spreadsheet and I've created some code to create a PivotTable, then format some names, then copy the data to a new sheet.

    What I'd like to do is instead of copying all of the data to a single new sheet is create a new sheet per company name, and copy just that companies data to that sheet.

    I can obviously record a macro of me doing this for each company by using the filter in the PivotTable, but the resulting code from the macro is not dynamic, i.e. each time I do this the company name is "hard coded" into the code. This is not use if, next month for example, the data changed and there are more/less/different companies to work with.

    Is there any way of making this "dynamic"? So that each month, regardless of the data set, I can just click a button to execute the "RunAll" macro and it does what I'm looking for?

    Many thanks!

  • Re: Using VBA to dynamically use filters

    This code will create a sheet for each company. If the sheet exists it will use the existing sheet

  • Re: Using VBA to dynamically use filters

    That's great, thanks.

    All I need to do now is figure out how to loop through each of the sheets your code creates and apply the code I came up with to produce the PivotTables.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!