Loop Through Worksheets Where Cell Contains Text & Copy Cells from 1 Sheet to the Others

Howdy,

So, I have a Worksheet used to process quotes from suppliers, whereby there is a Pulldown to make a selection for the supplier. Based upon assistance from this forum previously, I have implemented code such that when the user selects the pulldown the first time, it will ask the user "do you wish to add another supplier" and if the user clicks "Yes" it will copy the sheet and allow the user to change the selection (each new sheet MUST contain a different supplier, as I also have code which copies the supplier & renames the sheet to match that supplier). This can occur for as many suppliers who submit a quote (ie, there can be 1-N suppliers).

I also have a cell named "PART_NUM" that acts as the name of the entire workbook (I have code whereby once the user clicks "save" then it takes the text in "PART_NUM" & uses that to save as the Excel file (ie, the name of the workbook). For example, if the user types "dog" into cell PART_NUM, then clicks "Save", the code will rename the workbook "Dog.xlsm"

All of that works beautifully.

What I would like to do is the following:

1. The way it works right now, after the user performs the initial "save" (ie, "Dog.xlsm") - if the user keeps the workbook open & decides to change the value in PART_NUM (say, to "Cat") & then click "Save" again, the code will change the name of the Workbook & save a new file (ie, "Cat.xlsm"). That is all well & good - however, what is NOT happening is that cell PART_NUM in the other sheets is not getting updated from "Dog" to "Cat". So, if I were to open the new file "Cat.xlsm" and click through from Sheet 1 - Sheet "N", only the first Sheet would have Cell PART_NUM changed from "Dog" to "Cat" - I would like for the new value "Cat" in PART_NUM to be propagated to however many sheets that exist in the workbook... except (see #2, below)
   
2. Another wrench in the works - Because the user can create "N" number of sheets based on how many times he answers "Yes" to the Pulldown question (based upon how many suppliers exist), the total number of Sheets in each workbook can vary. Also, if an OLD version of the file exists w/ the same name, I have instructed the user to copy the sheets from the older Excel file & paste them to the new one (there is no naming collision because my code creates "Dog.xlsm" whereas if there is a pre-existing workbook, it will have previously been named "Dog.xls". However, my new sheets look completely different from the old ones & ALWAYS have a constant in Cell A1 (ie, "PREPARED BY"), so I was thinking of a loop which said something like "While Sheets.A1.Range("PREPARED BY"), then copy the text from SUPPLIER in the 1st sheet to SUPPLIER in all other sheets containing "PREPARED BY" in A1

Attached is my workbook, please peruse it & let me know if you have any questions.

Any & all help would be appreciated!!

Thanks,
Rob

OK, so I figured it out. The code is posted below for those who are facing similar issues.

Please Login or Register  to view this content.

I am calling this Sub from a "Before Save" event, so that when the user clicks "Save" - any changes he has made to the "Part Number" (ie, PART_NUM) will be propagated across all worksheets, and because I put the "If" statement in there, it propagates the changes to all the NEW sheets, but NOT the older ones which are formatted differently (the older ones do NOT have text in C1, which is why I can use the logical ...And WS.Range("C1") <> "" Then...


Even though it answers both questions I had (it copies the information to the sheets I want, but not the sheets I don't want), I have a question that I'm curious about, regarding the statement of code I have commented out above:

Please Login or Register  to view this content.

Why will this not work? It gives me a 1004 error: Method Range of Object _Worksheet failed

Anyway, just wanted to say that I'm proud that my knowledge of VBA is growing, and I thank the ppl on the forum for assisting in expanding my knowledge. So, thanks to everyone who has helped me w/ my questions, and who have answered questions of others such that those of us w/ similar questions & issues can learn!!