使用当月 VBA 的日期在行中填充一系列单元格，然后在下个月的第一天保存数据并清除工作表

Populating a range of cells in row with dates of the current month VBA, then save data and clear sheet at the first of the next month

提问by MicheleL

I am looking for a way to write VBA script that will automatically populate sheet17.range("f14:aj14") with dates of whatever the current month is. The date format needs to be mm/d/yyyy and I need to be able to use "FoundCell" method to find "Date" using this range. I found this code and adapted to my needs but it populates the next column to the right starting with the cell on the next row. I have tried changing up the "Offset" numbers and cannot get it to populate my range of cells, and as I am a novice with VBA, I have a macro to copy the sheet to new sheet, save new sheet and then clear contents of range(f15:AN73) of the original, I don't know the code for making it happen automatically at the first of each month at 12:00am. Here is the code I have for date population:

我正在寻找一种编写 VBA 脚本的方法，该脚本将使用当前月份的日期自动填充 sheet17.range("f14:aj14") 。日期格式需要为 mm/d/yyyy，并且我需要能够使用“FoundCell”方法使用此范围查找“Date”。我找到了这段代码并适应了我的需要，但它从下一行的单元格开始填充右侧的下一列。我曾尝试更改“偏移”数字，但无法让它填充我的单元格范围，而且由于我是 VBA 新手，我有一个宏可以将工作表复制到新工作表，保存新工作表，然后清除其中的内容range(f15:AN73) 的原始版本，我不知道让它在每个月的第一天凌晨 12:00 自动发生的代码。这是我的日期人口代码：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim Sht As Worksheet
    Dim intDaysInMonth As Integer
    Dim i As Integer

    Set Sht = Worksheets("MAR") 'change to suit
    Target = Sht.Range("f14:aj14")
    intDaysInMonth = Day(DateSerial(Year(Now()), Month(Now()) + 1, 0))

    Target.Resize(intDaysInMonth, 1).ClearContents
    For i = 1 To intDaysInMonth
        Target.Offset(i - 0, 1) = DateSerial(Year(Now()), Month(Now()), i)
    Next i
    Set Sht = Nothing
End Sub

I just need the dates to populate my sheet17.range("f14:aj14") instead of the next column to the right beginning at 1 cell down, and then the code to automatically save sheet to new sheet and clear the contents of the original sheet.range("f15:an73"). Any help would be greatly appreciated! Thank you in advance.

我只需要填充我的 sheet17.range("f14:aj14") 的日期，而不是从 1 个单元格向下开始的右侧下一列，然后是自动将工作表保存到新工作表并清除原始内容的代码sheet.range("f15:an73")。任何帮助将不胜感激！先感谢您。

Michele

米歇尔

采纳答案by DaveU

Here's the answer to the date fill part of your question. And, you need to place this in a regular module, NOT in a worksheet module.

这是您问题的日期填写部分的答案。而且，您需要将其放在常规模块中，而不是放在工作表模块中。

Sub myDates()

Dim Sht As Worksheet
Dim intDaysInMonth As Integer
Dim i As Integer
Dim Target As Range

Set Sht = Worksheets("MAR")   'change to suit
intDaysInMonth = Day(DateSerial(Year(Now()), Month(Now()) + 1, 0))

Set Target = Sht.Range("f14").Resize(, intDaysInMonth) 'set target to days count
Target(1).Resize(, 31).ClearContents 'clear all previous

For i = 1 To intDaysInMonth
    Target(i).Value = DateSerial(Year(Now()), Month(Now()), i)
Next i
Set Sht = Nothing

End Sub

But to make this more useful, you should call this from another sub, with the worksheet name as an argument, so you can access the correct month's worksheet. Then it would look like this:

但是为了使它更有用，您应该从另一个子调用它，并将工作表名称作为参数，以便您可以访问正确的月份工作表。然后它看起来像这样：

Sub myDates(ShtName As String)

Dim Sht As Worksheet
Dim intDaysInMonth As Integer
Dim i As Integer
Dim Target As Range

Set Sht = Worksheets(ShtName)   'change to suit
intDaysInMonth = Day(DateSerial(Year(Now()), Month(Now()) + 1, 0))

Set Target = Sht.Range("f14").Resize(, intDaysInMonth) 'set target to days count
Target(1).Resize(, 31).ClearContents 'clear all previous

For i = 1 To intDaysInMonth
    Target(i).Value = DateSerial(Year(Now()), Month(Now()), i)
Next i
Set Sht = Nothing

End Sub