☰

每周一在 Excel Online 中复制电子表格

tag-icon • tag-icon microsoft-excel
每周一在 Excel Online 中复制电子表格

我想知道是否有人知道每周一复制 MS Excel Online 工作表的方法。尝试研究 Power Automate，但它仅限于添加新行，甚至没有时间表/间隔。我想做的是 - 在同一个 Excel 文档中指定一个工作表并使其每周一复制。

答案1

有了这个宏，一切皆有可能。

新表将获取旧表的名称并添加周数。

将宏添加到您的文件并保存为 .xlsm 文件。

Private Sub Workbook_Open()
 Call CopySheet_Monday
End Sub

Sub CopySheet_Monday()
    Dim ws As Worksheet
    Dim MySheet As Worksheet
    Dim strMySheet As String
    Dim TodaysDay As String
    Dim NewSheetName As String
    
    'here you specify your worksheet name
    strMySheet = "MyWorksheet"
    
    Set MySheet = ThisWorkbook.Worksheets(strMySheet)
    
    'the name of the new worksheet. Old Name and weeknumber
    NewSheetName = MySheet.Name & Application.WorksheetFunction.IsoWeekNum(Date)
    
    'Check if new worksheet already exists
    For Each ws In Worksheets
        If ws.Name = NewSheetName Then 'exit sub when it exists
            Exit Sub
        End If
    Next ws
    
    With Application.WorksheetFunction
        TodaysDay = .Weekday(Date, 1) '1 if first day of week is sunday, 2 if it is monday
    End With
    
    If TodaysDay = 1 Then '1 is monday
        'copy sheet insert at the end
        MySheet.Copy after:=Sheets(Sheets.Count)
        ActiveSheet.Name = NewSheetName
    End If

End Sub

相关内容