Duplicating a worksheet

Question

I'm trying to set up a spreadsheet that duplicates the previous worksheet into a new tab daily.

I have it set up to create a new worksheet and rename it daily, but I can't figure out the duplication aspect.

Sub AddDayWkst()
Dim ws As Worksheet
Dim strName As String
Dim bCheck As Boolean

On Error Resume Next
strName = Format(Date, "mm-dd-yy")
bCheck = Len(Sheets(strName).Name) > 0

If bCheck = False Then
    Set ws = Worksheets.Add(Before:=Sheets(1))
    ws.Name = strName
End If

End Sub

I need to duplicate the previous day's worksheet and paste it into the new worksheet.

Instead of adding a new worksheet, just copy the previous days one? — BigBen, Nov 05 '19 at 20:52
Yes, that's correct. I want it to duplicate the previous days' tab and retitle it with the current days date. — Divest, Nov 05 '19 at 22:40
Just use `Worksheet.Copy` then, like [this question](https://stackoverflow.com/questions/8439657/copy-an-entire-worksheet-to-a-new-worksheet-in-excel-2010) shows. — BigBen, Nov 05 '19 at 23:29

score 1 · Accepted Answer · answered Nov 05 '19 at 23:34

Sub AddDayWkst()

    Dim ws As Worksheet
    Dim strNewName As String, strOldName As String
    Dim bValid As Boolean

    strNewName = Format(Date, "mm-dd-yy")
    strOldName = Format(Date - 1, "mm-dd-yy")

    bValid = WorksheetExists(strOldName)

    If bValid Then

        Set ws = Sheets(strOldName)
        ws.Copy before:=Worksheets(1)
        Worksheets(1).Name = strNewName

    End If

End Sub

Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean

    Dim sht As Worksheet

    If wb Is Nothing Then Set wb = ThisWorkbook

    On Error Resume Next

    Set sht = wb.Sheets(shtName)
    On Error GoTo 0

    WorksheetExists = Not sht Is Nothing

End Function

Duplicating a worksheet

1 Answers1