时间戳

首页 > 解决方案 > 更改文件名和日期

excel - 更改文件名和日期

问题描述

我有一个从工作表复制并粘贴到目标工作表的宏。数据表会发生变化（我们每周都会收到一份报告），但目标表始终相同。这两张纸可以保存在同一个工作文件夹中。

正在更改的 Excel 工作表名称的格式是

XXX 版本_XXX XXXXXX XXXXX FY2022 XXXX WE 11-10-2021

此文件名上的日期始终是运行报告的前一个星期一。

这就是我使用记录宏的方式。

Sub Macro1()

'Macro1 Macro
Sheets. Add After:=ActiveSheet

Windows ("Report that is received data, date”).Activate
With ActiveWorkbook.SlicerCaches ("Slicer Month")
.SlicerItems ("1/09/2021").Selected = True
.Slicertems ("1/07/2021").Selected = False
.SlicerItems ("1/08/2021").Selected = False
.SlicerItems ("1/10/2021").Selected = False
(etc)
End With
With ActiveWorkbook. SlicerCaches ("Slicer_department")
.Sliceritems ("Category1").Selected = True
.Sliceritems(“Category2”). Selected = False.
.Sliceritems(“Category2”). Selected = False.
(etc)
End with
ActiveWorkbook.SlicerCaches ("Slicer_manager")
.SlicerItems ("manager1").Selected = True
.Slicertems ("manager2").Selected = False
.SlicerItems ("manager3").Selected = False
(etc)
End with
range(“F22:M22”).select
selection.Copy 
Windows(“sheet where data is going”).Activate
Range(“A1”).Select
Activesheet.Paste

以上重复3次，分别复制和粘贴三个单独的行。（A1，A2，A3）。

标签： excelvbadate

解决方案

这将有助于在您的代码中获取上个月的开始，以及在切片器中选择上个月。按照您的方式，您必须不断添加新月份，因为它们会添加到您的数据中。

在复制/粘贴方面，有大量关于此的帖子，取决于您是否需要清除现有内容。

Dim lastMonth As Date
Dim startLastMonth As Date
Dim startLastMonthSlicerTxt As String
Dim startLastMonthFileTxt As String
Dim fileName As String

' this would be the generic part of the file name without the date
fileName = "my file name "

' subtract a month from current date
lastMonth = DateAdd("m", -1, Date)
' set the date to the 1st of last month
startLastMonth = DateSerial(Year(lastMonth), Month(lastMonth), 1)
' convert the date to text so you can use in slicer name
startLastMonthSlicerTxt = Format(startLastMonth, "d/mm/yyyy")
' convert the date to text so you can use in file name
startLastMonthFileTxt = Format(startLastMonth, "d-mm-yyyy")

' this appends the date and file extension to your generic file name
fileName = fileName & startLastMonthTxt & ".xlsx"
' as per your code, we now activate the Window but this time using the dynamic file name
Windows(filename).Active

' loop through the slicer items and only select if value matches last month
With ActiveWorkbook.SlicerCaches("Slicer_Month")
    For Each si In .SlicerItems
        If si.Name = startLastMonthTxt Then
            si.Selected = True
        Else
            si.Selected = False
        End If
    Next si
End With

推荐阅读