PDA

View Full Version : help debugging macro for my daily time tracker - copy/paste sheet at front of wk_book



sschwant
01-10-2021, 12:08 PM
This macro works, but I'd prefer to have the newly copied sheet placed near the front of the workbook (after the "Template" tab), rather than at the end of the workbook (scrolling right is time consuming after several months) as it would normally do. See line for 'After:=wshL, which I tried to replace with After:=Worksheets("Template"). This works fine for the first iteration when there is only 1 tab dated "10-09-20". However on the second iteration it breaks down b/c that sheet name is already in use (you can see this by deleting tab "10-09-20" and re-running 'Add_New_Sheet'). File attached - Excel 2016.

Thanks in advance for any suggestions!
Steve


Sub Add_New_Sheet()
Application.ScreenUpdating = False
Dim wshL As Worksheet
Dim wshN As Worksheet
Dim d As Date
Set wshL = Worksheets(Worksheets.Count)
d = DateValue(wshL.Name)
'wshL.Copy After:=wshL
wshL.Copy After:=Worksheets("Template")
Set wshN = ActiveSheet
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