所有单元格位置保持不变,唯一改变的是文件名中的日期。
我正在从一个Excel工作簿剪切并粘贴到另一个工作簿。
当文件名每天都在更改时,我如何执行所需的功能?
ActiveWindow.SmallScroll Down:=108
Windows("Standard 15OCT23.csv").Activate
Range("A2:H17").Select
Selection.Cut
Windows("Generic 15OCT23.csv").Activate
Rows("122:122").Select
Selection.Insert Shift:=xlDown
ActiveWindow.SmallScroll Down:=141
更新
我今天来上班,试图在今天的报告上运行这个,它不会工作。在对代码进行了一些重大编辑之后。我有一个应该工作的代码,但它就是不想合作。
Sub StandardToGenericDataXfer()
Dim sDate As String
sDate = UCase(format(Date, "ddMMMyy")) ' Format the current date
Dim standardWorkbook As Workbook
Dim genericWorkbook As Workbook
Dim wb As Workbook
' Loop through open workbooks
For Each wb In Workbooks
Dim wbName As String
wbName = UCase(wb.Name)
If (InStr(wbName, "STANDARD") > 0 Or InStr(wbName, "GENERIC") > 0) And _
InStr(wbName, sDate) > 0 And InStr(wbName, ".CSV") > 0 Then
If InStr(wbName, "STANDARD") > 0 Then
Set standardWorkbook = wb
ElseIf InStr(wbName, "GENERIC") > 0 Then
Set genericWorkbook = wb
End If
End If
Next wb
' Check if both workbooks were found
If Not (standardWorkbook Is Nothing) And Not (genericWorkbook Is Nothing) Then
' Copy data from standardWorkbook to genericWorkbook
standardWorkbook.Sheets(standardWorkbook.Name).Range("A2:H16").Copy
genericWorkbook.Sheets(genericWorkbook.Name).Range("A122").Insert Shift:=xlDown
' More Copy/Insert commands here
MsgBox "Data transfer complete.", vbInformation
Else
Dim message As String
message = "One or both workbooks not found:" & vbCrLf
If Not standardWorkbookFound Then message = message & "Standard workbook not found." & vbCrLf
If Not genericWorkbookFound Then message = message & "Generic workbook not found."
MsgBox message, vbExclamation
End If
End Sub
我没有遇到的问题是,即使我打开了两个文件,例如“Generic 15OCT23”和“Standard 15OCT23”,我的JavaScript代码现在也无法找到打开的文件。
2条答案
按热度按时间m528fe3b1#
Date
函数提供当前系统日期。Format
函数使用所需的日期格式将日期转换为字符串。Date function
Format function
顺便说一句,这篇文章绝对值得阅读。
How to avoid using Select in Excel VBA
代码可以简化如下。顺便说一句,
Cut
w/oPaste
不做任何更改。yqkkidmi2#
标识打开的工作簿