Renaming new worksheet using date and name

Hi Matthew

The problem is that the cell D1 has a formula '=TODAY()' as a result the date in the cell is not available on the other sheet.

I have added some additional code, to let you know that the date has not been found.

Sub rename()
    Dim wsMain As Worksheet, wsSum As Worksheet
    Dim rng As Range
    
    Set wsMain = Worksheets(1)
    Set wsSum = Worksheets(2)
    wsMain.Name = Replace(wsMain.Range("D1"), "/", "") & " " & Left(wsMain.Range("B2"), 5)
    For Each rng In wsSum.Range("A3:AE3")
        If wsMain.Range("D1") = rng.Value Then
            col = rng.Column
            Exit For
        End If
    Next rng
    If col = "" Then
        MsgBox "The date " & wsMain.Range("D1") & " cannot be found on the summary sheet"
    Else
        For n = 1 To wsMain.UsedRange.Rows.Count
            wsSum.Cells(3 + n, col) = wsMain.Cells(1 + n, "F")
        Next n
    End If
End Sub

Regards