Problem inserting new worksheet in Excel daily time tracker w/ date format (at specific location in book)

[DocAElstein]

Hi Steve.
I was not sure myself how Excel handles hidden worksheets, so I ran this short macro in one of the modules in you last uploaded file,
daily time tracker - test.xlsm ,

Sub IWasntSuremyself()   '      https://excelfox.com/forum/showthread.php/2609-Problem-inserting-new-worksheet-in-Excel-daily-time-tracker-w-date-format-(at-specific-location-in-book)?p=14778&viewfull=1#post14778
Dim Ws As Worksheet
    For Each Ws In Worksheets
    Dim strOut As String  ' Name             Item Number                 Is it hidden or not                              New Line
     Let strOut = strOut & Ws.Name & "    " & Ws.Index & "   " & IIf(Ws.Visible = xlSheetHidden, "Hidden", "Not Hid") & vbCr & vbLf
    Next Ws
 Debug.Print strOut    ' from inside the VB Editor , hit keys  Ctrl + g  to get immediate window , from which you can copy the text
' or
 MsgBox Prompt:=strOut ' same output again, but you can't copy it
End Sub

That short macro gives this result
Reference 1 Not Hid
Activities & Macro 2 Not Hid
Template 3 Hidden
08-11-20 4 Not Hid
08-10-20 5 Not Hid
08-09-20 6 Not Hid
The file corresponding to those results , ( daily time tracker - test.xlsm ) , looked like this:
time tracker – testBefore.jpg : https://imgur.com/k4NNspv
time tracker - testBefore.JPG

So that is suggesting to me that I can possibly in VBA treat the hidden Template as a normal worksheet at position ( Index / Item number ) of 3 and with a name of Template
( I also ran that macro in the middle of the macro below, and it helped me figure out where the new copied worksheet was placed. It was not placed as I expected, after Activities & Macro , instead it came after Template . ( I found it strange that it put the worksheet after the hidden worksheet, but I guess that is just the way VBA works when a Hidden worksheet is there: The macro tells it to put the copied worksheet after Activities & Macro, but the results suggest it puts it one place further after the hidden worksheet, Template
results when doing this.jpg: https://imgur.com/pVn4wTx
Reference 1 Not Hid
Activities & Macro 2 Not Hid
Template 3 Hidden
Template (2) 4 Hidden
08-11-20 5 Not Hid
08-10-20 6 Not Hid
08-09-20 7 Not Hid
... .................... Wierd! )

So the basic coding to make the latest/newest date worksheet WshL and to copy Template worksheet and make a new one with the new date, would be, I think the following.

Sub Test4() '                                           '  https://excelfox.com/forum/showthread.php/2609-Problem-inserting-new-worksheet-in-Excel-daily-time-tracker-w-date-format-(at-specific-location-in-book)?p=14776&viewfull=1#post14776
Dim wshL As Worksheet, WshN As Worksheet, WshT As Worksheet
 Set wshL = Worksheets.Item(4)                          '  forth worksheet counting tabs ( including hidden)  from the left
 Set WshT = Worksheets.Item(3)                          '  third worksheet counting tabs ( including hidden)  from the left
 Set WshT = Worksheets.Item("Template")                 '  this does the same as the last code line - you don't need both lines,  either one of them will do
Dim d As Date
 Let d = DateValue(wshL.Name)                           '  date value of current forth tab
 
 WshT.Copy After:=Worksheets("Activities & Macro") '
' Call IWasntSuremyself                                  '  This tells me, that my new worksheet has item / index of 4
 Let Worksheets.Item(4).Visible = xlSheetVisible        '  I make the newely added worksheet visible
 Set WshN = Worksheets.Item(4)                          '  This is the newely added worksheet
 WshN.Name = Format(d + 1, "mm-dd-yy")                  '  the new worksheet gets a name of 1 day above the previous newest worksheet

 Worksheets("Activities & Macro").Activate              '  "get back" to left
End Sub

After running that macro a couple of times, you get this
time tracker - testAfter.JPG : https://imgur.com/DaJmHXA
time tracker - testAfter.JPG

( If you then run Sub IWasntSuremyself() , you get
Reference 1 Not Hid
Activities & Macro 2 Not Hid
Template 3 Hidden
08-13-20 4 Not Hid
08-12-20 5 Not Hid
08-11-20 6 Not Hid
08-10-20 7 Not Hid
08-09-20 8 Not Hid )


So at the end of that macro, you have
_ the new worksheet referred to by variable WshN, and it has a name of a new date. That worksheet is a copy of the template worksheet.
and
_ the worksheet that had previously the name of the newest date retains its date name and is referred to by the variable WshL


Alan

[sschwant]

Hey Doc,

I think you've done it! Couple of observations: 1) If the Template tab is not hidden, then the macro copies that tab - so will just have to make sure it stays hidden. 2) The pivot table on the newly created tab ends up pointing to a different tab and it should always only be pointing to the data validation table on the currently created new tab for each iteration . . . I think I can fix this ... I will tinker w/ it and circle back later today.

Thanks!!
Steve
p.s., your handle looks awfully close to Albert Einstein ... coincidence?