How to: Turn Summary sheet into individual detailed sheets?

[JPWRana]

At work we have been asked to stop using a summary sheet I had made years ago in favor of a new individual sheet. I like my sheet, but I have to go with the flow.

Is there a way for me to transcribe what's in my summary sheet into an individual detailed sheet?

My uploaded attachment has how the summary sheet looks like, and how each sheet is supposed to look like.
I even added a "Reference List" sheet that won't be in there in the future (unnecessary big file size), but just so the macro can be created.

In my final macro-enabled worksheet, the "Reference List" will be a separate excel document on a different folder.

Just as a note: Info from some of the columns come from formulas and parameter query's. I hope this doesn't change anything.

Edit: Forgot to add, that the sheets "1 - ###, 2 - ###, ..., 10 - ###" are how the populated sheets are supposed to look like.

[stnkynts]

I am assuming that the final workbook will only contain the "Summary Checksheet" and "Single Checksheet Template". You wish to have the individual worksheets created within the code.

Please Login or Register  to view this content.

Note: Due to the nature of your layout there is a variety of things that can go wrong. A lot of this can be fixed with error checking, which unfortunately takes a bit of time and testing to code in. I encourage you to learn how the code works and why it does what it does so that you can put in error checking and/or understand if things go haywire.

Note2: Double check everything as there was a lot of hand programming. Very tedious and prone to mistakes.

[JPWRana]

stnkynts, I will try this out first thing in the morning. Thank you for your BIG HELP!

[JPWRana]

stnkynts, it's almost there. There are a few things that came up.

1) What is the pop-up window in the beginning of the macro for?

2) On Structure number 7, on my upload, it is a pass. On your macro, it is a fail. The conditions for that specific scenario on the summary checksheet is the following: First there is a Pass, then there are 2 line items checked off for field conditions.

3) For Structure number 6, I have a note that on my upload it shows, but not on the macro. For coding purposes, do you want me to give it a code just like the others ones (like IFTS, NCAD, CMD, etc.)?

Let me know what you think.

[daffodil11]

I've made some additions to stnkynts's code using the formulas we discussed in https://www.excelforum.com/showthread.php?t=1052058

That the original script was hand programmed is pretty incredible.

I added:

Please Login or Register  to view this content.

And then for J5, J6, J9:

Please Login or Register  to view this content.

I wish I knew a more elegant way to vlookup without resorting to worksheetfunction. My Code-Fu is pretty weak.

Here's the updated workbook.

[stnkynts]

@daffodil. Thank you kind sir. I see now that J5, J6, and J9 come from the reference sheet.

@JPWRana. As daffodil pointed out, J5, J6, J9 come from the reference sheet. However, you indicated that the final product will not have the reference sheet in it and will instead be located in another workbook in another directory. This, to say the least, is a critical problem. In answer to your other questions:

1) What is the pop-up window in the beginning of the macro for?

It is a built in fail safe. As i indicated in my code you can remove it if you wish.

2) On Structure number 7, on my upload, it is a pass. On your macro, it is a fail. The conditions for that specific scenario on the summary checksheet is the following: First there is a Pass, then there are 2 line items checked off for field conditions.

Correction made in amended code by utilizing a boolean variable.

3) For Structure number 6, I have a note that on my upload it shows, but not on the macro. For coding purposes, do you want me to give it a code just like the others ones (like IFTS, NCAD, CMD, etc.)?

I arbitrarily assigned it the code QNR, which can be later altered in code to match your own code.

Let me know what you think.

I think this took me longer than I initially planned. Like I said, it is still prone to potential errors and I don't have the ambition at this moment to make it bullet proof.


Amended Code:

Please Login or Register  to view this content.