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

Sub New_Group_Setup()
'
' New_Group_Setup Macro
' Macro recorded 5/15/2012 by Kristin_Stout
'
' Keyboard Shortcut: Ctrl+Shift+N
'
    Sheets("Template").Select
    Sheets("Template").Copy Before:=Sheets(2)
    Sheets("Template (2)").Select
    Sheets("Template (2)").Name = "New Group"
    Sheets("Direct Cases Listing").Select
    Selection.End(xlDown).Select
    cLastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
    Cells(cLastRow, "A").Value = "New Group"
    ActiveCell.FormulaR1C1 = "New Group"
    cLastRow = Cells(Rows.Count, "B").End(xlUp).Row + 1
    Cells(cLastRow, "B").Value = "='New Group'!R[-4]C[8]"
    cLastRow = Cells(Rows.Count, "C").End(xlUp).Row + 1
    Cells(cLastRow, "C").Value = "='New Group'!R[5]C[2]"
    cLastRow = Cells(Rows.Count, "D").End(xlUp).Row + 1
    Cells(cLastRow, "D").Value = "='New Group'!R[6]C[1]"
    cLastRow = Cells(Rows.Count, "E").End(xlUp).Row + 1
    Cells(cLastRow, "E").Value = "='New Group'!R[7]C"
    cLastRow = Cells(Rows.Count, "F").End(xlUp).Row + 1
    Cells(cLastRow, "F").Value = "='New Group'!R[8]C[-1]"
    cLastRow = Cells(Rows.Count, "G").End(xlUp).Row + 1
    Cells(cLastRow, "G").Value = "='New Group'!R[9]C[-2]"
    cLastRow = Cells(Rows.Count, "H").End(xlUp).Row + 1
    Cells(cLastRow, "H").Value = "='New Group'!R[10]C[-3]"
    Range("A3").Select
    Sheets("New Group").Select
End Sub

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".

Sub NewGroups()

Dim wsTemplate As Worksheet
Dim wsNew As Worksheet
Dim wsDirect As Worksheet
Dim rngLink As Range
Dim strInput As String

Set wsTemplate = ActiveWorkbook.Sheets("Template")
Set wsDirect = ActiveWorkbook.Sheets("Direct Cases Listing")

   With Application
      .ScreenUpdating = False
      .EnableEvents = False
   End With

      Sheets("Template").Copy After:=Sheets(Sheets.Count)
      Set wsNew = ActiveSheet
         strInput = InputBox("Name of new group?", "New Group Name?")
         'If you really want each new group to be named "New Group" delete the
         'line above and uncomment the next line below
         'strInput = "New Group"
         wsNew.Name = strInput
      
      With wsDirect
         lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
         Set rngLink = .Range("A" & lngLastRow)
         rngLink.Value = strInput
         strLink = "=" & strInput & "!A1"
         wsNew.Hyperlinks.Add Anchor:=rngLink, Address:="", SubAddress:= _
                                    strLink, TextToDisplay:=strInput
         .Select
      End With
         
      rngLink.Offset(0, 1).Formula = "='" & strInput & "'!J1"
      rngLink.Offset(0, 2).Formula = "='" & strInput & "'!D10"
      rngLink.Offset(0, 3).Formula = "='" & strInput & "'!D11"
      rngLink.Offset(0, 4).Formula = "='" & strInput & "'!D12"
      rngLink.Offset(0, 5).Formula = "='" & strInput & "'!D13"
      rngLink.Offset(0, 6).Formula = "='" & strInput & "'!D14"
      rngLink.Offset(0, 7).Formula = "='" & strInput & "'!D15"

   With Application
      .EnableEvents = False
      .ScreenUpdating = False
   End With
    
End Sub

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