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

[DocAElstein]

... If the Template tab is not hidden, then the macro copies that tab - so will just have to make sure it stays hidden. ..

?? … I am not quite sure what you are saying there… or maybe I see what you mean....
If the Template is not hidden , then the last macro does more like I would normally expect and it puts the new copied worksheet after Activities & Macro. That chucks a spanner in the works because then the WshN variable gets Set to the Template which has just been shifted one space to the right by the insertion of the new worksheet. ... - The copied sheet was put in after Activities & Macro so it got into position 3, since [FONT=Courier New]Activities & Macro[/FONT is at position 2 . - The Template then has to shift over to position 4 to make space for the copied worksheet having being inserted at position 3.

With hindsight, it would be better to copy to after Template
I think then , the macro will work the same whether the template is hidden or not

Sub Test4b() '                                          ' 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=14779&viewfull=1#post14779
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
Dim d As Date
 Let d = DateValue(wshL.Name)                           '  date value of current forth tab
 
 WshT.Copy After:=Worksheets("Template") '
' 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

The simple macros currently are somewhat dependant on you fixing where your worksheets are initially. But it would not be too difficult to make sure they still work if your first few worksheets might be jumbled around randomly. You would probably just need an initial code section doing something like my test macro Sub IWasntSuremyself() to figure out where everything is.


I don’t recall giving much thought to my username: Based on my real name, title, and modified slightly to suit my current situation in Germany, I wrote it almost instantly the first time I needed a username …. So who knows !!! , .. I am not too sure in the meantime how I came to be.. here…