VBA to create multiple worksheets, rename, and insert information all off main sheet ...

I need to create multiple works sheets based of the part numbers in Column C. Each sheet will need to be renamed to the part number that is shown. Now in each sheet, I need the corresponding Part Number and Description block to auto populate each one. So if I should change the information on the "ALL" worksheet it will automatically change in its correspond Worksheet. My code is already Posted in the Visual Basic screen. I also have a Hyperlink formula in there to hyperlink each part number to the seperate Worksheet.

I need help creating the code below to do what i need it to.. and possibly combine it into one code.

Please Login or Register  to view this content.

Sample1.xlsm

There's always a hard way and an easy way. I know you're trying to do it all the hard way in VBA, but it's not necessary. Here's how I would accomplish this.

1) In your template, put these formulas:

A2: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
B2: =VLOOKUP(A2, All!$C:$E, 2, 0)

Don't worry about the error message, it won't matter later.

C1: =HYPERLINK("#ALL!A1", "Home")
This little addition will come in handy when you're workbook suddenly has 400 sheets in it.

2) On the ALL sheet, add another column:

F1: LINK
F2: =HYPERLINK("#'" & C2 & "'!A1", "Link")

Copy F2 down your dataset to create clickable links that WILL work after you run the macro.

2) Now erase your macros from the ALL sheet module, they don't really go there anyway.
Insert a standard code module (Insert > Module) and put in this macro:

Please Login or Register  to view this content.

You can run this version as many times as you wish because it will only create new sheets if they don't already exist.
As the sheets are created and named, the cells in A2 and B2 will fill themselves out.
Also, this macro will put the sheets into the same order in the workbook that they are listed in column C. Nifty little addition.



So, delete all the other sheets, save your workbook, then try it out.

DOH, my bad, correct the VLOOKUP formula on the template:

=VLOOKUP(A2, All!$C:$E, 3, 0)

No kudos for the "auto ordering of the sheets, too? Hehe.... glad it's working for you.