hello
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. 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'). Excel file (Excel 2016) attached.
Code
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
Thanks in advance for any suggestions!
Steve