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

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

My file is used for daily productivity & time tracking and is almost working as I'd like. Originally the macro would place the new sheet to the far right of all sheets in the book, but as it's iterated over the last month or so it's getting cumbersome to keep scrolling all the way to the left to the tab with my macro button ("Activities & Macro"). So, I started tinkering with a new file in which only 3 tabs to start: "Template", "Activities & Macro", and "08-05-20".

When I run the macro a new sheet is created right after the Activities & Macro tab as I'd like (this would mitigate the need to keep scrolling left after a new day/date tab is inserted). However, then there's a break down ... 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).

If I run macro again I end up with: 08-07-20 (2), 08-06-20, and 08-07-20 so now what was 08-06-20 is now 08-07-20 and the new sheet is 08-07-20 (2). This would iterate perpetually with every new sheet as a duplicate. Also, once the date is set - it should be static; e.g., first date = 08-05-20, next = 08-06-20, next = 08-07-20 so on for time tracking purposes ...

What am I missing? Thanks!

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313859#p313859
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313855#p313855
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313848#p313848
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313843#p313843
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313792#p313792
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313771#p313771
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313767#p313767
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313746#p313746
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313744#p313744
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313741#p313741
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313622#p313622
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313575#p313575
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313573#p313573
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313563#p313563
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313555#p313555
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533
https://www.eileenslounge.com/viewtopic.php?f=39&t=40265&p=313468#p313468
https://www.eileenslounge.com/viewtopic.php?f=42&t=40505&p=313411#p313411
https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313384#p313384
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313382#p313382
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313380#p313380
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313378#p313378
https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313305#p313305
https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

2 Attachment(s)

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

Quote:

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
Attachment 3341 https://i.imgur.com/RZ1h9l4.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/ )

Problem inserting new worksheet in Excel daily time tracker w/ at specific location

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

3 Attachment(s)

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
Attachment 3346 https://i.imgur.com/fCyhnux.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
Attachment 3347 https://i.imgur.com/1jRD3Jh.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 … )

2 Attachment(s)

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:
Attachment 3349
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!

3 Attachment(s)

Hi,

Quote:

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
Attachment 3351 https://i.imgur.com/DRRlWzR.jpg

This is After running the macro a few times
TimeTracker3After.JPG : https://imgur.com/tIZYGeD
Attachment 3352 https://i.imgur.com/tIZYGeD.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 )

1 Attachment(s)

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

3 Attachment(s)

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
Attachment 3355 https://i.imgur.com/k4NNspv.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
Attachment 3356 https://i.imgur.com/DaJmHXA.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

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?

Quote:

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… :)