Excel - 宏 - 根据日期值复制和粘贴数据

Question

我找到了以下代码，效果很好；但是，我必须手动更改月份，以便它转到第二个工作簿的右侧工作表。由于工作表以月为单位，我怎样才能使其自动更改为当月？

Sub AlarmSheet()
Dim wkb As Workbook, wks As Worksheet, LastRow As Long
Dim FilePath As String, FileName As String
Dim ws As Worksheet, blnOpened As Boolean

'Change these variables as desired...
FilePath = "B:\CARECENT\28 Day\" 'change path here
FileName = "Installs_Removals_2016.xlsm" 'change name here
    Call ToggleEvents(False)
Set ws = ThisWorkbook.Sheets("SHA") 'change source sheet name here
If WbOpen(FileName) = True Then
    Set wkb = Workbooks(FileName)
    blnOpened = False
Else
    If Right(FilePath, 1) <> Application.PathSeparator Then
        FilePath = FilePath & Application.PathSeparator
    End If
    Set wkb = Workbooks.Open(FilePath & FileName)
    blnOpened = True
End If
Set wks = wkb.Sheets("June") 'change destination sheet name here
LastRow = wks.Cells.Find(what:="*", after:=wks.Cells(1, 1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
wks.Cells(LastRow, "A").Value = ws.Cells(63, "B").Value
wks.Cells(LastRow, "B").Value = ws.Cells(6, "B").Value
wks.Cells(LastRow, "C").Value = ws.Cells(7, "B").Value
wks.Cells(LastRow, "I").Value = ws.Cells(9, "B").Value '& "," & ws.Cells(10, "B").Value & "," & ws.Cells(11, "B").Value
wks.Cells(LastRow, "J").Value = ws.Cells(10, "B").Value
wks.Cells(LastRow, "N").Value = ws.Cells(11, "B").Value
wks.Cells(LastRow, "O").Value = ws.Cells(15, "B").Value
wks.Cells(LastRow, "P").Value = ws.Cells(60, "B").Value

If blnOpened = True Then
wkb.Close SaveChanges:=True
End If
'If MsgBox("Clear values?", vbYesNo, "CLEAR?") = vbYes Then
    'Call ClearData
End If
Call ToggleEvents(True)
End Sub

Answer 1

您可以将“June”替换为 Format(Date, "mmmm")这将为您提供当前日期的月份。