excel - 复制工作表

Question

我正在尝试设置一个电子表格，每天将以前的工作表复制到一个新标签中。

我已经将它设置为创建一个新的工作表并每天重命名它，但我无法弄清楚重复方面。

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

我需要复制前一天的工作表并将其粘贴到新的工作表中。

Answer 1

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