Macro to create new worksheets and auto populate cells in created worksheets

[cole.young]

Hi All.

I have been working on a quoting spreadsheet to use in quoting machine shop jobs. Please see attached. I've nearly gotten it (I think) except for a small thing that I cant figure out how to do.

On the first tab "Quote Info", the user enters part no's, quote number and a part description. I have a macro that will copy my "calculator" worksheets (part cost and shop time both required for each part number) and rename each sheet with the part number and a "PC" or "ST" prefix (for part cost or shop time). However, I need the functionality to go a bit further. Once the macro generates a pair of worksheets for a given part number, I would like it to auto-fill the "Quote No", "Part No" and "description" fields at the top of each created worksheet with the info that corresponds from the "Quote Info" worksheet.

So the end result would look like this: (made up info here)

Quote number 1127
Part No: Description
123 Lever Handle
345 Mounting Bracket
678 Blade Guard

*Click "Generate Calculators" button

Worksheets PC 123, ST 123, PC 345, ST 345, PC 678 and ST 678 are created

On the PC 123 worksheet, the quote number field would read 1127
Part no field would say "123"
Desc field would say "Lever Handle"

I cant figure out how to do this. Any help would be appreciated.

[skywriter]

Try this.

Please Login or Register  to view this content.

[cole.young]

Skywriter - Great work! Works just like I needed it to. Thank you so much.

[skywriter]

My pleasure, thanks for the feedback and reputation points.

[cole.young]

Found something that I forgot to mention.

Some of the formulas on the PC worksheets refer to calculated values that are stored in the ST worksheets. When the new worksheets are created via the macro, the formulas still refer to the calculated fields in the template worksheets. Is there a way to make the formulas update too?

This also happens when the "Customer Quote" worksheet tries to look for values to populate itself with.

[skywriter]

It would be helpful if you did the leg work and gave me specifics so I don't have to look around and then run the risk of missing one.

[cole.young]

Of Course.

If you open the attached workbook, you can see that I created a test part in the quote info page and generated a pair of the sheets for this PN. If you refer to the worksheet "PC-555" cells C29, 31 and 33 have a formula that looks like this "=IF('Shop Time Calculator'!J12=0,0,'Shop Time Calculator'!J12)" which refers back to the template worksheet. The formula should say "=IF('ST-555'!J12=0,0,'ST-555'!J12) so that it refers to the shop time sheet it just created. This situation occurs for all of the cells I just mentioned.

If you look at the "customer quote" sheet, the qty colum has the formula "=IF('Part Cost Calculator'!$C$7="","",'Part Cost Calculator'!$C$7)" in cell C16. This formula should read "=IF('PC-555'!$C$7="","",'PC-555'!$C$7). A similar issue happens in the price column on this same worksheet.

Each row of the customer quote worksheet is intended to correspond to a part number in the quote info worksheet. So each row going from top to bottom on the customer quote sheet should refer to the created worksheets for that part number. Hopefully that makes sense.

[skywriter]

I'm not sure what you mean with the customer quote sheet, you didn't mention that before.

If you mock up a sheet with several part numbers and several new sheets and show me what you want on the customer quote sheet, I'll see what I can do.

As far as the other request, that's just a matter of replacing the text in the formulas.

Please Login or Register  to view this content.

[cole.young]

Thanks Skywriter. I think I can use what you posted above to fix the customer quote sheet. I'll take a look at it in the morning and see if I can figure it out.

[cole.young]

I wasn't quite sharp enough to figure out what you did and be able to modify it to fix this issue. But I've created a copy of the spreadsheet with some dummy data in it so I can better explain the issue.

On the PC-123 sheet that was created via your code above, you can see that the cost per part at the bottom of the sheet is $8.49. Next, take a look at row 16 on the customer quote sheet. The formula in cell A16 is "=IF('Shop Time Calculator'!$C$2="","",'Shop Time Calculator'!$C$2". The code needs to replace "shop time calculator" in this formula with "ST-123" and etc, moving down the column for each additional part number that might initially be typed into the quote info sheet. Cell B16 has formula "='Quote Info'!D8" and this needs to update to refer to the description row on the quote info sheet and should correspond to each part number that is entered into the quote info sheet. Cells C16 and D16 on the customer quote sheet also have formulas that contain references to "part cost calculator" sheet. These formulas also need to update to refer to "PC-123" sheet that was created by your code earlier.

Hopefully that made sense. Thanks again for your help. I really appreciate it.

[cole.young]

I have finally had some more time to give this speadsheet some love. There are a couple of things I haven't been able to figure out, though, and I am hoping someone will assist me.

The spreadsheet I have uploaded has some test data in the "Quote Info" sheet.

Start by clicking the "Generate Quote Calculators"

First problem I cant solve: The sheet opens a dialogue window asking for update values for sheet "PC-555" (which is created when you click the "generate Quote Calculators" button). This shouldn't happen. I am not sure why it is doing this. Click "cancel" to continue.

Next issue: On worksheet "ST-555" that is created, cell j26 has a #Ref error. If I input values into the cells that the formula looks at and then click on j26 and then click another cell (To get j26 to "refresh"), the formula works. This should work without having to manually make the cell refresh/recalculate.

Last issue: On the "Customer Quote" worksheet, the price column should refer to cell K44 of the corresponding PC (Part cost)worksheet, so in this example, the value in cell D16 on the "customer quote" worksheet should refer to cell k44 of sheet PC-555. I dont know how to make the formulas in the price column update to refer to the correct sheet once the macro has created them.

Please post if I didnt explain this correctly.

Thanks so much for any help received, I will gladly award you a sum of fake internet points for your troubles