vba - 被宏复制后更改单元格值

Question

下载工作表以更好地理解。
https://www.dropbox.com/s/urncoww8prj6rc1/AccountabilityScorecardDigital.xlsm?dl=0

我正在创建一个具有自动创建工作表的工作簿。除日期外，每张表都有相同的数据。我只需要一个单元格“A1”将其名称自动更改为下一个日期范围。

For Example: Sheet1 Cell A1 "DEC 11-15 2017" 
    Copied : Sheet2 Cell A1 "Dec 18-22 2017"
   Copied2 : Sheet3 Cell A1 "Dec 25-29 2017"
   Copied3 : Sheet4 Cell A1 "Jan 01-05 2017"

它必须遵循此命名约定，以便工作表的其余部分相应更新。

Answer 1

干得好。

只需使用 GetProperWeekName发挥作用并随心所欲地喂它。

例如，GetProperWeekName("DEC 11-15 2017", 7)
将返回:
DEC 18-22 2017
您可以使用可选参数添加天、月或年。

要添加一周，您可以使用 7就像我在我的例子中所做的那样。

创建新工作表时，只需更新 A1通过执行以下操作:
Range("A1") = GetProperWeekName(Range("A1"),7)

Function GetProperWeekName(TheWeek As Variant, Optional DaysToAdd = 0, Optional MonthsToAdd = 0, Optional YearsToAdd = 0) As String
Dim TheDate As Date
TheDate = DateValue(Trim(Left(TheWeek, WorksheetFunction.Find("-", TheWeek) - 1)))
If DaysToAdd <> 0 Then TheDate = DateAdd("d", DaysToAdd, TheDate)
If MonthsToAdd <> 0 Then TheDate = DateAdd("m", MonthsToAdd, TheDate)
If YearsToAdd <> 0 Then TheDate = DateAdd("y", YearsToAdd, TheDate)
GetProperWeekName = UCase(Format(TheDate, "mmm")) & " " & Day(TheDate) & "-" & Day(DateAdd("d", 4, TheDate)) & " " & Year(TheDate)
End Function

结果:



如果您希望它接受正常日期并对其进行格式化，则更容易:

Function GetProperWeekName(TheDate As Date, Optional DaysToAdd = 0, Optional MonthsToAdd = 0, Optional YearsToAdd = 0) As String
If DaysToAdd <> 0 Then TheDate = DateAdd("d", DaysToAdd, TheDate)
If MonthsToAdd <> 0 Then TheDate = DateAdd("m", MonthsToAdd, TheDate)
If YearsToAdd <> 0 Then TheDate = DateAdd("y", YearsToAdd, TheDate)
GetProperWeekName = UCase(Format(TheDate, "mmm")) & " " & Day(TheDate) & "-" & Day(DateAdd("d", 4, TheDate)) & " " & Year(TheDate)
End Function

如何正确更新工作表和日期范围:

Sub UpdateSheet()
ActiveSheet.Copy After:=Sheets(ActiveSheet.Name)
ActiveSheet.Name = GetProperWeekName(Range("A1"), 7)
Range("A1") = GetProperWeekName(Range("A1"), 7)
End Sub

QHarr 的填充添加:

Function GetProperWeekName(TheWeek As Variant, Optional DaysToAdd = 0, Optional MonthsToAdd = 0, Optional YearsToAdd = 0) As String
Dim TheDate As Date
TheDate = DateValue(Trim(Left(TheWeek, WorksheetFunction.Find("-", TheWeek) - 1)))
If DaysToAdd <> 0 Then TheDate = DateAdd("d", DaysToAdd, TheDate)
If MonthsToAdd <> 0 Then TheDate = DateAdd("m", MonthsToAdd, TheDate)
If YearsToAdd <> 0 Then TheDate = DateAdd("y", YearsToAdd, TheDate)
GetProperWeekName = UCase(Format(TheDate, "mmm")) & " " & Format(Day(TheDate), "00") & "-" & Format(Day(DateAdd("d", 4, TheDate)), "00") & " " & Year(TheDate)
End Function