Macro to Create separate sheet for each Unique Value in Col G and put SUMIFs Formula

Hello All,

I am using Excel 2010 and have attached a sample of my problem

I have two Sheets Data and Master

Data Sheet has repeated Store Names in Col G

I want the macro to copy the Master Sheet as per unique Store Names in Col G

It should place the value from Data Sheet E5 to G2 and Sheet name in Cell L2 of each sheet
Eg in the attached there would be total 21 Sheets i.e Master plus 20 Sheets for each Store

Then I would need the SUMIFS Formula in Col F to Col J on all the Sheets

Col F : =SUMIFS(Data!$K$5:$K$84,Data!$B$5:$B$84,Master!C5,Data!$F$5:$F$84,Master!$L$2)
Col G : =SUMIFS(Data!$K$5:$K$84,Data!$B$5:$B$84,Master!C5,Data!$F$5:$F$84,"<>Reject")
Col H : =SUMIFS(Data!$H$5:$H$84,Data!$B$5:$B$84,Master!C5,Data!$G$5:$G$84,Master!$L$2)
Col I: =SUMIFS(Data!$I$5:$I$84,Data!$B$5:$B$84,Master!C5,Data!$G$5:$G$84,Master!$L$2)
Col J: =SUMIFS(Data!$J$5:$J$84,Data!$B$5:$B$84,Master!C5,Data!$G$5:$G$84,Master!$L$2)

Hope to get some help

Thanks in advance

Rashid

Try this. The only two-step in the process is a small pivot table that gets the unique values for the store name. The names are "overlaid" with a named dynamic name. So you can change store names and the number of stores. Just make sure the pivot table refreshes.

Otherwise I loop through the sheets and the actual sheet names to delete any sheets with those names so when I try to create them, I won't get an error.

Hello prkhan56,

This VBA macro will create one sheet for each location using the "Master" as a template. The attached workbook has the macro added and a button on the "Master" to run it.

Please Login or Register  to view this content.

Hello prkhan56,

The macro has been changed to delete a unique sheet if it exists. replace the macro code in your workbook with the code below. The attached workbook has the change also.

Please Login or Register  to view this content.

Hello prkhan56,

You're welcome.