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

**sschwant** · 08-08-2020, 12:59 AM

I have the following code:

Code:

 
Sub 
Application.ScreenUpdating = False
    Dim wshL As Worksheet
    Dim wshN As Worksheet
    Dim wshP As Worksheet
    Set wshP = Worksheets("Activities & Macro")
    Dim d As Date
 '   Set wshL = Worksheets(Worksheets.Count)
 '   d = DateValue(wshL.Name)
 '   wshL.Name = Format(d + 1, "mm-dd-yy")
    wshL.Copy After:=Worksheets("Activities & Macro")
    Set wshN = ActiveSheet
    d = DateValue(wshL.Name)
    wshN.Name = Format(d + 1, "mm-dd-yy")
    Worksheets("Template").Columns("B:D").Copy wshN.Range("A1")
    wshN.Range("E2").PivotTable.SourceData = _
    wshN.Range("A1").CurrentRegion.Address(, , xlR1C1, True)
    ActiveWindow.Zoom = 90
    Application.ScreenUpdating = True
End Sub

**DocAElstein** · 08-08-2020, 12:50 PM

Hello sschwant
Welcome to ExcelFox

Your current macro renames the last worksheet to a new ( day +1 ) date and then copies it. So this … Tab 08-05-20 gets renamed as 08-06-20 and the new sheet (inserted after Activities & Macro tab) is named 08-06-20 (2) …. is exactly what I would.

Code:

    Set wshL = Worksheets(Worksheets.Count)           '  Last tab
    d = DateValue(wshL.Name)                          '  date value of current last tab name
    wshL.Name = Format(d + 1, "mm-dd-yy")             '  add 1 day to date value and rename last tab name to that new date
    wshL.Copy After:=Worksheets("Activities & Macro") '  copy the last  tab

Your macro then renames the new worksheet to a new date( day +1 ) of the already renamed worksheet) date.
So the new worksheet gets a date two days ahead of the original last worksheet, and that original worksheet has a date one day ahead

Code:

    d = DateValue(wshL.Name)                          '  the original last tab has already had its name changed to one day ahead
    wshN.Name = Format(d + 1, "mm-dd-yy")             '  the new worksheet gets a name another day ahead, two days in total above the original last worksheet

Originally Posted by sschwant

What am I missing?

I can’t tell you what you are missing, because you have not explained you want to do.

Let me take a stab at guessing what you might want to do, ......

I will assume that after running the macro, the new worksheet with the newest date is the third worksheet, after the second worksheet ( Activities & Macro ) . Effectively then all the other worksheets get shifted to the right. - So you would end up with something like this:
Newest at Third tab.JPG : https://imgur.com/RZ1h9l4
Newest at Third tab.JPG

This macro does that:

Code:

Sub Test2() '  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=14763#post14763
Dim wshL As Worksheet, wshN As Worksheet, wshP As Worksheet
 Set wshP = Worksheets("Activities & Macro")
 Set wshL = Worksheets.Item(3)                     '  3rd tab worksheet
Dim d As Date
 Let d = DateValue(wshL.Name)                      '  date value of current third tab
 
 wshL.Copy After:=Worksheets("Activities & Macro") '  copy the third  tab, place the new copy  at the third tab position, ( which will shift all other worksheets after it one space to the right )
 Set wshN = ActiveSheet                            '  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("Template").Columns("B:D").Copy wshN.Range("A1")
'    wshN.Range("E2").PivotTable.SourceData = _
'    wshN.Range("A1").CurrentRegion.Address(, , xlR1C1, True)
'    ActiveWindow.Zoom = 90
End Sub

Alan

( cross post : https://www.excelforum.com/excel-pro...-location.html
https://www.mrexcel.com/board/thread...-file.1142030/ )

**sschwant** · 08-11-2020, 01:14 AM

Alan,

Thanks - your code is working, but isn't exactly what I was trying to do ... My file is meant to be a daily time tracker. There are 4 tabs to start:
1) "Task List" - this is static and for reference only,
2) "Activities & Macro" - also static w/ a button to launch the macro,
3) "Template" - also static, but meant to be copied as a new date formatted daily tracker/time keeper. It has a table w/ 3 columns B: time by by 15 minute increments, C: data validation drop down list referring to the list of activities on the tab # 2, and D: hours = 0.25 per row.
4) "08-09-20" - first date formatted tab. To start this can be blank (I think). Ultimately though it would be a copy of the Template tab but w/ a simple Pivot Table appended to it - which summarizes the activities by a sum of hours and count of activities - which is included in the code already.

The goal is to have the template copied as a new date formatted tab w/ the pivot table, inserted to the right of the Activities & Macro tab. Then as the cycle iterates - all previous dates are pushed off to the right. Initially, all new sheets were inserted at the far right of the workbook, but as the process iterates, it gets cumbersome to keep scrolling way left to get back to the macro button on the Activities & Macro tab.

So, is there are a way to edit your macro such that it copies the template?

Thanks!
Steve

**DocAElstein** · 08-11-2020, 12:20 PM

Hi,
I think I am probably still not quite sure what it is that you are after: I do not understand fully your explanation. Initially you said your file had 3 tabs to start with.. Now it has 4
Never mind.
In this next attempt, the worksheet looks like this initially
TimeTracker2Before.JPG : https://imgur.com/fCyhnux
TimeTracker2Before.JPG

The macro copies the template giving it the name of date + 1 day of the last worksheet name date. It inserts that at the last tab position

Code:

' 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=14771&viewfull=1#post14771
Sub Test3() '
Dim wshL As Worksheet, wshN As Worksheet, wshP As Worksheet
 Set wshP = Worksheets("Activities & Macro")
 Set wshL = Worksheets.Item(Worksheets.Count)                                          '  last worksheet
Dim d As Date
 Let d = DateValue(wshL.Name)                                                          '  date value of current last tab
 
 Worksheets("Template").Copy After:=Worksheets(Worksheets.Item(Worksheets.Count).Name) '  copy the Template tab, place it at the last tab position
 Set wshN = ActiveSheet                                                                '  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 macro a few times you will have like this
TimeTracker2After.JPG : https://imgur.com/1jRD3Jh
TimeTracker2After.JPG

If you need more help, then you would probably be best to upload two files. One, the Before, should show what you have before the macro is run, and then the second , the After, should show exactly the situation as it should be after the macro is run. And explain carefully exactly what you did to make the After wordbook

Alan

( P.S. Most forums have a “cross posting rule”. You should tell everyone everywhere where else you post the same question … )

**sschwant** · 08-11-2020, 11:57 PM

That's working but I want the new tab to be at the front - pushing older tabs to right. I tried changing the copy line from After to Before, but that only works once. I've attached a sample workbook; green tabs would be created by the macro (produced manually in this case). Yellow tabs indicate starting point for the file. Should look like this:
time tracker.PNG
Also, w/in the work book module1 - test sub is some of the code that should be used to append a pivot table to the new sheet.
HTH

p.s., cross posted on ExcelForum, answers.microsoft and MrExcel ... little traction there.

Thanks!

**DocAElstein** · 08-12-2020, 02:20 PM

Hi,

Originally Posted by sschwant

That's working but I want the new tab to be at the front - pushing older tabs to right.

That sound similar to the first macro I gave.

If you insert a worksheet at any point, then all existing tabs after are automatically shifted to the right. (The tabs before it stay as they are ). As far as I know that is the default what happens if you don’t try specifically to do anything else.

The tabs on your latest file seem to have changed again. This time we have no Template worksheet, just Reference , Activities & Macro, and then the made files.
( You uploaded a .xlsx file, so there are no macros in it.)

So I am back to guessing again what you want.
Let me assume that the last added file, in your example tab 08-11-20 is the one that you want to copy as if it was a template.
So this next macro will take the third tab as being both the previous worksheet and the template.

Code:

Sub Test3b() '                                          '  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=14773&viewfull=1#post14773
Dim wshL As Worksheet , WshN As Worksheet
 Set wshL = Worksheets.Item(3)                          '  Third worksheet counting tabs from the left
Dim d As Date
 Let d = DateValue(wshL.Name)                           '  date value of current third tab
 
 wshL.Copy After:=Worksheets("Activities & Macro") '
 Set wshN = ActiveSheet                                 '  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




































' To change date default short format from English to USA:
' http://www.eileenslounge.com/viewtopic.php?f=27&t=34444      regedit in serch window after hitting Microsoft Symbol, then after aggreeing to admin prompt,  navigate  HKEY_CURRENT_USER  Control Panel  International  then double click on  sShortDate   and edit as rtequired

This is Before:
TimeTracker3Before.JPG : https://imgur.com/DRRlWzR
TimeTracker3Before.JPG

This is After running the macro a few times
TimeTracker3After.JPG : https://imgur.com/tIZYGeD
TimeTracker3After.JPG

We seem to be starting to go around in circles.
Possibly you should have a good think about what it is you actually want to do.

The Problem of inserting a new worksheet at a specific location in a book is very simple to solve, and we have done that now a couple of times successfully. Something else is possibly getting us mixed up, and I am not sure what that is.

Alan

(P.S. can you give me the link to the post you did at “answers.microsoft” please. – I am not very familiar with that place, so I don’t know where to look for your cross post,. Thanks )

**sschwant** · 08-12-2020, 05:28 PM

Sorry about that file (didn't realize the macros were in another open workbook). I've got them now in 2 modules in the attached .xlsm file. The Template tab is hidden in the workbook - and it should be the one that gets copied by the macro.

Also, here are links to 2 posts on answers.microsoft.com's Office forum:
https://answers.microsoft.com/en-us/...b-de16b7b8fcc8
https://answers.microsoft.com/en-us/...c-5a203eb50ad3
The second was my original post and was appended w/ an issue I was having with saving/re-opening the file.
You may need to login to this forum using your Microsoft account credentials (not sure - you certainly do to post in the first place).

Thanks!
Steve

**DocAElstein** · 08-13-2020, 02:40 PM

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 ,

Code:

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.

Code:

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** · 08-13-2020, 07:39 PM

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?

**DocAElstein** · 08-14-2020, 12:28 PM

Originally Posted by sschwant

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

Code:

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…