Create copy of multiple worksheets and automatically fill in data from a main sheet

What I have is master sheet that I use to generate batch sheets for lab experiments. Typically I have to create each batch sheet by hand by using a macro to create multiple copies of a template. Then I go through to each batch sheet and do a copy and fill operation by hand to get the information from the main sheet. The problem is that I need to do this very often with up to 100 experiments. What I want to do is automate the entire process using a macro that creates the copies and subsequently auto fills the sheets based upon the tab number. Inside the attached workbook is a macro that does the copy process, what I need to do is modify this to also do the copy and fill process.

I don't recall where I came across the copy macro, but I do not know how to use VBA well at all.

Any help would be appreciated. Thank you.

Allen

Hello NMChemEng,

After downloading your file and trying to run it, Excel crashes. The file appears to be corrupted. I think you should re-zip the file and post it again.

Sincerely,
Leith Ross

My apologies, it looks as if some plug-ins for ASPEN were trying to load.

Allen

Hello NMChemEng,

I realize you can do this in your sleep if you have to do this for up to 100 experiments. Can you give me a step by step using just one template? I'm looking at this and getting lost.

Thanks,
Leith Ross

I populate the master sheet with the experiments of interest.
I select the template of interest.
Then edit the macro CMW to create the sheets for the experiments of interest. Then run the macro CMW.
I then go to the created sheets and individually select cell A51 and set it to point to the line the corresponds to the batch (count on the master sheet) of the tab. Then do a fill to fill the rest of row 51 with the data for that batch. Then repeat for each sheet.

The CMW macro will duplicate the selected tab and number them from based upon the counter for the macro. I Edit this macro to have the counter run for the batches needed.

Was the above helpful?

Thanks,

Allen