我希望能够选择一行并将其复制并粘贴(使用命令按钮)到保持关闭状态的已关闭工作簿中。以下代码复制范围以下的所有内容。我只想复制我选择的行。
这些是需要更改的行:
With ThisWorkbook.Worksheets("Sheet1")
With .Range(.Range("A4:Q4"), .Range("A4:Q4").End(xlDown))
destSht.Cells(destSht.Rows.Count, 1).End(xlUp).Offset(1).Resize(.Rows.Count, .Columns.Count).Value = .Value
完整代码:
Sub copytoarchive()
Dim destSht As Worksheet
Workbooks.Open ("C:\...\FileToCopyTo.xlsx")
Set destSht = ActiveWorkbook.Worksheets("Sheet3")
With ThisWorkbook.Worksheets("Sheet1")
With .Range(.Range("A4:Q4"), .Range("A4:Q4").End(xlDown))
destSht.Cells(destSht.Rows.Count, 1).End(xlUp).Offset(1).Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
End With
destSht.Parent.Close True
End Sub
答案1
也许是这样的:
Sub copytoarchive()
Dim destSht As Worksheet
Selection.Copy
Workbooks.Open ("C:\...\FileToCopyTo.xlsx")
Set destSht = ActiveWorkbook.Worksheets("Sheet3")
destSht.Activate
Range("A1").Select
destSht.Cells(destSht.Rows.Count, 1).End(xlUp).Offset(1).Select
ActiveSheet.Paste
destSht.Parent.Close True
End Sub
它复制、打开目标表、按照您已经执行的方式找到下一行,然后粘贴并关闭。