LOG that keeps a summary of new tabs

**Hooty** · 09-26-2014, 02:53 PM

I have a log file that just keeps being added to. i want to create a template Log that has a sheet with a summary of the different "EX" tabs. where each line has a link to the correct Tab.
i'd like to click a button to copy the template sheet in numeric order:

PHP Code:


Public Sub SheetCopy()



Dim Sh As Worksheet, TemplateSh As Worksheet

Dim ShNum As Integer, HighestNum As Integer

Dim SheetCoreName As String



' INDICATE THE CORE SHEET NAME

SheetCoreName = "EX "



' INDICATE THE SOURCE SHEET

Set TemplateSh = Sheets("Template")





' DETERMINE NEXT NUMBER FOR SHEET

For Each Sh In Worksheets

If InStr(1, Sh.Name, SheetCoreName) = 1 Then

    ShNum = Val(Right(Sh.Name, Len(Sh.Name) - Len(SheetCoreName)))

    If ShNum > HighestNum Then HighestNum = ShNum

End If

Next Sh



' COPY TEMPLATE

TemplateSh.Copy after:=Sheets(Sheets.Count)



' MAKE VISIBLE

ActiveSheet.Visible = xlSheetVisible



' RENAME

ActiveSheet.Name = SheetCoreName & HighestNum + 1



End Sub



Private Sub CommandButton1_Click()

Run "SheetCopy"

End Sub

Then i want to be able to copy the line in the summary sheet down 1 row and maintain the links, only to the new sheet.

**JBeaucaire** · 09-28-2014, 01:12 PM

Simplest approach is to change the formulas in the EXTRAS sheet to use INDIRECT() reference to the target sheets. I've updated a few of the formula in row 9 to demonstrate this using the value in A9 to complete the indirect sheet reference.

Now, row 9 must always be there to start, it will be the "template" to the first sheet and will be used as a "row template" to create additional rows with the same functions the same way the TEMPLATE sheet is being used to create new sheets with the same layout.

I've moved the SheetCopy macro out of the sheet modules an into a public module where it is easily found. Here's my version of that macro:

Please Login or Register  to view this content.

This macro:

1) Copies the last current EX row in the table and insert the copy into a new row below the last
2) Deletes all the constants in that row, leaving only the formulas you've created.
3) Adds a new higher number in column A of the new row
4) Unhides the template, copies it, hides the template and names the new sheet.

**Hooty** · 09-29-2014, 10:45 AM

That is perfect! thank you!

**JBeaucaire** · 09-29-2014, 10:51 AM

As it appears you've reached a conclusion, I've marked this thread SOLVED for you.
FYI, this is done through the Thread Tools located above the first post in this thread. Thanks.