I have a macro that adds new sheets depending on the previous name of the previous one. So here we have day shifts and night shifts so each day has 2 sheets allotted to it. There is a template sheet that this macro copies and uses to make the new ones. It looks at the previous sheet name
(ex: 06-17-2019 DS). This is for June 17th Day Shift. If I run the macro it will make a sheet called 06-17-2019 NS for June 17th Night Shift. It will run for as many days as I tell it to (this is referenced in another sheet in the workbook).
My problem I'm having is that I want it to automatically open the proper sheet once it is done running. If I run the macro for "7 days" then it will open up on 06-24-2019 D. But I want to call this days shift.
Ex: the shift starting is 06-17-2019 DS but there is no sheet for this shift yet, the last sheet is called 06-16-2019- NS. So I run the macro, it adds sheets then opens the last one 06-24-2019 DS. I don't want it to open that sheet, I want to open 06-17-2019 DS (the next sheet).
Thanks in advance
Sub add_sheets() Dim wb As Workbook Dim ws As Worksheet Dim lastws As Worksheet Dim LastSheet As String Dim lastdate As Date Dim lastshift As String Dim lastcrew As String Dim no_newsheets As Integer Dim newdate As Date Dim newcrew As String Dim newshiftcount As String Dim newshift As String Dim newsheetname As String Dim startdate As Date Dim Crew As String Dim range_cal As Range Dim TrucksRowNumber As Integer Dim CrewRowNumber As Integer Dim CopyRange As Range Dim ws_lookup As Worksheet Dim rRange As Range, rngToCopy As Range Dim lRow As Long Dim ws_last As Worksheet Dim bRow As Long Dim cRow As Long If ActiveWorkbook.MultiUserEditing Then MsgBox "Please unshare the workbook prior to adding new sheets." & vbNewLine & vbNewLine & "Review Tab > Share Workbook > Uncheck the Box (Allow changes by more than one user) > OK" & vbNewLine & vbNewLine & "Remember to turn sharing back on after you are done!", 48, "Running Repair Call List is Currently Shared" Else Sheets("Template").Visible = xlSheetVisible Set wb = ActiveWorkbook Set ws = Sheets(1) Set ws_lookup = Sheets("Lookup Table") For Each ws In ActiveWorkbook.Worksheets If ws.Name Like "*DS" = True Or ws.Name Like "*NS" = True Then Set lastws = ws Else End If Next ws lastws.Select LastSheet = lastws.Name lastdate = Left(LastSheet, Len(LastSheet) - 3) lastshift = Right(LastSheet, 2) startdate = lastdate no_newsheets = Sheets("lookup table").Cells(10, 3).Value 'Number of days to generate new sheets for newdate = lastdate Do While newdate <= startdate + no_newsheets '-1 If lastshift = "DS" Then newshift = "NS" newdate = lastdate ElseIf lastshift = "NS" Then newshift = "DS" newdate = lastdate + 1 End If 'Set oWS = ActiveWorkbook.Worksheets.Add MySheetName = Format(newdate, "mm-dd-yyyy") & " " & newshift 'formats the name of sheet Sheets("Template").Copy Before:=Sheets("Template") ActiveSheet.Name = MySheetName Set ws_last = ActiveSheet lastdate = newdate lastshift = newshift Loop End If 'Josh DeMont commented this out, we don't need this message box to pop up 'If Not ActiveWorkbook.MultiUserEditing Then ' MsgBox "Please reshare the workbook after adding new sheets." & vbNewLine & vbNewLine & "Review Tab > Share Workbook > Check the Box (Allow changes by more than one user) > Yes or OK to pop up windows", 48, "Running Repair Call List is not Currently Shared" 'End If Sheets("Template").Visible = xlSheetHidden End Sub