Rename Sheet Based on List from Another Sheet

[magneto_sphere]

Hello All,

I need to write a macro that will create a new sheet and name that sheet based on a list from another sheet.

Sheet1 range A1:A250 have product names, range B1:B250 have codes that match those product names. I will have a schematic (new sheet) for each product name and code in the workbook. When the user creates the new schematic for the next product, I'd like the sheet name to be "product name + product code".

Is there a way I can get the result I need?

Thank you for all the help!

[Arkadi]

What is supposed to trigger the creation of the new sheet? Or do you just want a macro that makes 250 new sheets with the desired name?

[magneto_sphere]

The user will trigger the change by clicking a button that says "Create New Schematic" which we already have built, but need the renaming feature added in. And the user only needs to create 1 sheet at a time because the result of the schematic will effect inventory for the next schematic created.

[Arkadi]

Assuming you have a variable "myRow" that identifies the row for the part:

Please Login or Register  to view this content.

[magneto_sphere]

No I do not have a variable "myRow" that identifies the row. I guess I need to define myRow to be the next row in the list. How do I get that accomplished?

Could it reference off the current schematic back to the list?

[Arkadi]

that depends on what your schematic is... if the schematic sheet has the part name and or number, we may not even need to use the sheet1 values to name the new sheet...

if you are looking for help integrating it into existing code, can you provide the code you are working with? We need to have something that lets the code know what line to refer to on your Sheet with part numbers and names, if we are going to use it. "Next" row in the list is a problem if you don't do all the work in one shot, because the code would not know where you left off yesterday, if you re-open the workbook today.

[magneto_sphere]

Sent you a pm

[Arkadi]

Got it, have to do a few things but will review it as soon as I can and see what we can do or need to add...

[Arkadi]

magneto... anywhere near the start of your new schematic code put this code...

Please Login or Register  to view this content.

That should give you the values for Product Name and Code# for the row below the one that relates to the "Current Schematic" meaning the one where you click the button at the time.
I assume with those variables, you will be able to do the code for filling A6 and I5 and rename the sheet?

You may want to do some additional coding so that a message comes up if the variables are still empty after the for k loop has run.. since that means there was no match for the name/code combination, or it was working off the last row already.

[magneto_sphere]

Arkadi,

Thank you so much for the help. I've applied the code and it works perfectly! Exactly what I was needing. I am going to write up a message box as you suggested.

Rep point added. Mods, please mark this as [SOLVED].

magneto... anywhere near the start of your new schematic code put this code...

Please Login or Register  to view this content.

That should give you the values for Product Name and Code# for the row below the one that relates to the "Current Schematic" meaning the one where you click the button at the time.
I assume with those variables, you will be able to do the code for filling A6 and I5 and rename the sheet?

You may want to do some additional coding so that a message comes up if the variables are still empty after the for k loop has run.. since that means there was no match for the name/code combination, or it was working off the last row already.

[Arkadi]

Magneto,
Im glad it worked out
It is up to you as thread initiator to mark it as solved.