Loop through two validation lists + copy and paste output to new workbooks and sheets

  • Sorry if this isn’t accepted, but i’d like to take the below thread and try to modify it to work for the functionality I’m working to accomplish…



    This thread takes the two data validations and cycles through all the potential options and then copies and pastes the values for each iteration on the Output sheet. What I’m trying to accomplish is to take data validation 1 (A-D in this example) and create a new workbook and with data validation 2 (1/1/2021 - 1/10/2021) create the 10 sheets titled 1/1/2021 - 1/10/2021 and copy the sheet (ie in this case Input sheet) and paste on to the respective sheets within each of the four workbooks that are created. So in this case it would be 4 workbooks with 10 sheets.


    My data is 7 departments (A-G) that would be data validation 1 and data validation 2 containing the months of the year. So my output would be 7 workbooks with 12 sheets each titled January - December. Ideally I’d love it to take the values of cells A1:I10 and paste values and A11:I60 and paste formulas.


    Hope this makes sense!

  • Go to Best Answer
  • I have the code working within it's own workbook where it will create a new sheet for each option in the data validations. I cannot, for the life of me, figure out where to add the workbooks.add code (and probably settings) to create individual workbooks for each department. Can someone smarter than I help figure this last piece out?


    Attached also is my test version.


    Thanks in advance!


  • soooo close yet soooo far.....


    Code now will open the workbooks and copy the sheets however it can't save correctly.


    • Best Answer

    Holy cow! I figured it out! :P


    Even though neither of them will probably ever know it, but thanks to Carim for the path to the solution and oberyn for having a similar problem. :)


  • Bpark

    Selected a post as the best answer.

Participate now!

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