VBA按日期创建sheet

最新推荐文章于 2024-12-30 15:11:12 发布

原创最新推荐文章于 2024-12-30 15:11:12 发布 · 964 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#vba #excel

编程语言专栏收录该内容

28 篇文章

订阅专栏

本文介绍了一段VBA代码，该代码可以按指定日期范围内的每个工作日创建新的Excel工作表副本，并将副本放置在最后一个工作表之后。此外，还为每个新创建的工作表设置了名称，并为特定单元格分配了计算估值日期的值。

Sub duplicateWorksheetByDate()
    Dim ws As Worksheet
    Dim firstSheet As Worksheet
    Dim endDate As Date
    Dim di As Date
    Dim wd As Integer
    Dim valuationDate As Date
    endDate = "11/01/2021"
    Set firstSheet = worksheets.Item(1)
    Set ws = worksheets.Item(worksheets.Count)
    For di = "10/20/2021" To endDate
        wd = Weekday(di)
        If wd <> 1 And wd <> 7 Then
            firstSheet.Copy After:=ws
            Set ws = worksheets.Item(worksheets.Count)
            ws.Name = Format(di, "dd mmm yyyy")
            If wd = 2 Then
                valuationDate = DateAdd("D", -3, di)
            Else
                valuationDate = DateAdd("D", -1, di)
            End If
            ws.Cells(2, 4).Value = valuationDate
            ws.Cells(3, 4).Value = valuationDate
        End If
    Next di
End Sub