Create new worksheets, name, and link

I don't know anything about writing in VB, I tried to record my macro, but it's not working right for me. So, now I'm trying to tweak the code, but need some help.

I started a workbook to keep all of the information for each of our direct brokerage cases. The idea is to have a worksheet for each group, and a table of contents with hyperlinks to each page. Also on the table of contents I’m trying to pull in some specific fields from the group’s worksheet (renewal dates).

I have a template page built. I want my macro to copy and rename the template page for each new group, then to add formulas to the table of contents that pulls information from the group's worksheet.

The first problem I encountered was:
My macro works the first time I use it, then when I try to add a second group it, instead of adding the information to a new line, it overwrites my first line.

Then I searched the internet for some formulas (shown below in red) to find the next free row, but now every time it starts one row down, it’s also pulling the data from the other tab one cell down…. I tried recording the macro using the Absolute Cells (Example $A$1), but that’s not working.

This is one way to get the next free row, assuming column B here
cLastRow = Cells(Rows.Count,"B").end(xlUp).Row+1
to access that cell, use
Cells(cLastRow,"B").Value = ....

See attached image of what results I'm getting vs. what I need to happen
Excel.JPG

Please Login or Register  to view this content.

I think I spent way too much time on this, and maybe the solution is simple. Please let me know if you can help, or have any questions about what I’m trying to do. Thank you so much!!

Attached is the excel document. When you open it, press Ctrl+Shirt+N to run the macro (the first one works), then rename the “New Group” tab to something else, and run the macro again, and you’ll be able to see the problem.

Please edit your title to "Create new worksheets, name, and link" (It is a rule to have clear, concise titles).

See if this accomplishes what you are after. Make a copy of your workbook and test the following on the copy. Paste into your module and reassign the New Group button to the NewGroups sub.

I took the liberty of using an input box to gain the users input as to the name to be assigned to the new group (and subsequently the new worksheet name). I couldn't think of reason why all new groups would literally be named "New Group".

Please Login or Register  to view this content.

I am unable to recreate the error on my side. How many 'new groups' did you add? MS says there is a bug in the copy method of worksheet class when many copies are made of the same worksheet (template).

http://support.microsoft.com/kb/210684