macro to add unknown number of tabs

[marcos.cabrera]

Hello,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I'm working on an Excel tool that would allow me to visualize the status of different projects. Each project is contained in a separate tab named after its position (there are at the moment 12 projects so the next new project will be in a tab named "13", the next on a tab "14" and so on). There's also a first tab named "overview" which has a table where all the project names and some information about the projects. Note that this "overview" tab doesn't contain actual data, but links to the project tabs.
Since the file will be used by other people, I'm working in a macro to automate the process of adding a new project, which entails the following:
1. Creating new tab and renaming it accordingly (note that besides the project tabs there are other tabs which are not relevant for the macro. Note also that project tabs are the only named with only numbers).
2. Copying the project template into the new tab from the "template tab"
3. Dialog box asks for project information (name, person in charge, department) and copies it into cells in new project tab
4. Adding a new row in tab "overview" table and setting the links from new project tab.
<o> </o>
So far I’ve managed to do what I assume is the easy part: creating dialog box, buttons, and using macro recording for getting the code lines of the “copy, paste, add row, add new tab, etc” stuff.
What I’m missing is the part where the macro knows what number comes next to rename the tab of new project, in what position should the macro add the new row in “overview” tab, setting up the links in the new row from the new tab (there could be 20 new more projects in the future or maybe 2, I can’t know that).
<o> </o>
Can someone please give me a hand? I would much appreciate it.
Hope the explanation works!

 

[Ralajer]

Welcome to the board cabrera.

The below macro should work. You will have to change some specifics as I didn't have all the information I needed.

Sub addProjectSheet()

    Dim numShts As Integer
    Dim numProjectShts As Integer
    Dim numOtherShts As Integer
    Dim newWS As Worksheet
    Dim hlRng As Range
    
    '// The number of non Project #'d sheets (Overview, Template, Etc)
    numOtherShts = [COLOR="red"][B]2[/B][/COLOR]
    '// Total Number of sheets
    numShts = Sheets.Count
    '// Number of Project sheets
    numProjectShts = numShts - numOtherShts
    '// Copy the template tab and place at end of tabs
    Sheets("Template").Copy After:=Sheets(numShts)
    '// Set new sheet to variable
    Set newWS = Sheets(Sheets.Count)
    '// Change the name to the number of projects precopy plus 1
    newWS.Name = (numProjectShts + 1)
    '// Find the last row containing data in link column and set range
    '//  to the next row after that (Example Column "A" is Link Column)
    Set hlRng = Sheets("Overview").Cells(Rows.Count, "[COLOR="red"][B]A[/B][/COLOR]").End(xlUp).Offset(1, 0)
    '// Create hyplerlink to newly created sheet from previously defined range
    hlRng.Hyperlinks.Add Anchor:=hlRng, _
                            Address:="", _
                            SubAddress:="'" & newWS.Name & "'!A1", _
                            TextToDisplay:="[B][COLOR="red"]Project [/COLOR][/B]" & newWS.Name
End Sub

Let me know if that works outs.