Change the name of the sheet having a dynamic date

Question

I want to duplicate a sheet that has a dynamic sheet name containing today's date. However, I'm facing naming the new duplicated sheet. What do I input instead of the last line?

Sub Duplicate()

  ' Duplicate Macro
  ' duplication of OIP sheet

      Dim myDate
      myDate = Date
      Dim LValue As String
      LValue = "OIP " & Format(myDate, "mm.dd.yyyy")


      Sheets(LValue).Select
      Sheets(LValue).Copy Before:=Sheets(7)
      Sheets(LValue (2)).Name = "Advanced Filters"

      End Sub

Answer 1

It would be best to add the date to the "Advanced Filters" worksheet to make it consistent with the original worksheet's naming convention.

Say you manage to create the "Advanced Filters" worksheet, how are you going to reference it later on in code?

It takes a little extra time but you should create functions to return the names of the worksheets and functions to return references to the them. The work done up front will pay off in the long run.

Usage

Dim TodaysOIPAdvancedFilters As Worksheet
Set TodaysOIPAdvancedFilters = OIPAdvancedFiltersWorksheetByDate(Date, True)

Refactored Code

Sub Duplicate()
    OIPAdvancedFiltersWorksheetByDate Date, True
End Sub

Public Function OIPWorksheetByDate(DateOf As Date) As Worksheet
    On Error Resume Next
    Set OIPWorksheetByDate = ThisWorkbook.Worksheets(OIPWorksheetName(DateOf))
    On Error GoTo 0
End Function

Public Function OIPAdvancedFiltersWorksheetByDate(DateOf As Date, Optional CreateIfNotExists As Boolean) As Worksheet
    On Error Resume Next
    Set OIPAdvancedFiltersWorksheetByDate = ThisWorkbook.Worksheets(OIPAdvancedFiltersWorksheetName(DateOf))
    On Error GoTo 0
    If OIPAdvancedFiltersWorksheetByDate Is Nothing And CreateIfNotExists Then
        Dim SourceWorksheet As Worksheet
        Set SourceWorksheet = OIPWorksheetByDate(DateOf)
        
        If SourceWorksheet Is Nothing Then
            MsgBox "OIP Worksheet for " & DateOf & " not found", vbCritical, "Action Cancelled"
            Exit Function
        End If
        On Error Resume Next
        SourceWorksheet.Copy Before:=SourceWorksheet
        ThisWorkbook.Worksheets(SourceWorksheet.Index - 1).Name = OIPAdvancedFiltersWorksheetName(DateOf)
        Set OIPAdvancedFiltersWorksheetByDate = ThisWorkbook.Worksheets(SourceWorksheet.Index - 1)
        On Error GoTo 0
    End If
End Function

Private Function OIPWorksheetName(DateOf As Date) As String
    Const DateFormat As String = "\O\I\P mm.dd.yyyy"
    OIPWorksheetName = Format(DateOf, DateFormat)
End Function

Private Function OIPAdvancedFiltersWorksheetName(DateOf As Date) As String
    Const DateFormat As String = "\O\I\P mm.dd.yyyy \A\d\v\a\n\c\e\d \F\i\l\t\e\r\s"
    OIPAdvancedFiltersWorksheetName = Format(DateOf, DateFormat)
End Function

ADDENDUM

Use this sub to compare the worksheet names in the immediate window:

Sub CompareNames()
    Const DateFormat As String = "\O\I\P mm.dd.yyyy"
    Debug.Print "["; ActiveSheet.Name; "]"
    Debug.Print "["; Format(#11/10/2020#, DateFormat); "]"
End Sub

Check for extra spaces and make sure that the I in OIP is not an L.