Excel VBA - 选择要从中复制并粘贴到下一个空白行的现有工作簿的工作簿

Excel VBA - choose workbook to copy from and paste into existing workbook on next blank row

提问by user3108483

I am new to VBA and have been using the site to piece together a solution.

我是 VBA 新手，一直在使用该站点来拼凑解决方案。

I need to write a macro that prompts the user to open a file (wb2), copy a row of data from a Sheet1 in that workbook (wb2) and then paste it into the next empty row within the original workbook (wb) also on Sheet1. I got it to work up until I tried adding the code for pasting in the next empty row - I am now receiving the following error message, "Run-time error '438': Object doesn't support this property or method"

我需要编写一个宏，提示用户打开一个文件 (wb2)，从该工作簿 (wb2) 中的 Sheet1 复制一行数据，然后将其粘贴到原始工作簿 (wb) 中的下一个空行中表 1。我让它开始工作，直到我尝试在下一个空行中添加用于粘贴的代码 - 我现在收到以下错误消息，“运行时错误‘438’：对象不支持此属性或方法”

Any help would be greatly appreciated.

任何帮助将不胜感激。

Sub test()
Dim wb As Workbook, wb2 As Workbook
Dim ws As Worksheet
Dim vFile As Variant

'Set source workbook
Set wb = ActiveWorkbook

'Open the target workbook
vFile = Application.GetOpenFilename("Excel-files,*.xlsx", _
    1, "Select One File To Open", , False)

'if the user didn't select a file, exit sub
If TypeName(vFile) = "Boolean" Then Exit Sub
Workbooks.Open vFile

'Set selectedworkbook
Set wb2 = ActiveWorkbook

wb2.Range("A3:E3").Select
Selection.Copy
wb.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True

wb2.Close

'Set targetworkbook
Set wb = ActiveWorkbook

End Sub

回答by sbanders

Just a quick note on the subject: Instead of

关于这个主题的简短说明：而不是

wb2.Worksheets("Output").Range("J3:R3").Select
Selection.Copy

try

尝试

wb2.Worksheets("Output").Range("J3:R3").Copy

Also Instead of

也代替

wb.Worksheets("Master").Range("C" & LastCellRowNumber).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

try

尝试

wb.Worksheets("Master").Range("C" & LastCellRowNumber).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Often times, Selectcreates unexplainable errors. Particularly when working with multiple workbooks, try to stay away from Select. This code comes almost directly from working code I have. Let us know if this doesn't fix the problem.

很多时候，Select会产生无法解释的错误。特别是在处理多个工作簿时，尽量远离Select. 这段代码几乎直接来自我拥有的工作代码。如果这不能解决问题，请告诉我们。

回答by user3108483

I re-worked the code and got it working. It's probably not the cleanest way to do it, but given my timeline and lack of VBA knowledge, it will have to do.

我重新编写了代码并使其正常工作。这可能不是最干净的方法，但鉴于我的时间表和缺乏 VBA 知识，它必须这样做。

Many thanks to engineersmnky for their help.

非常感谢工程师的帮助。

Description: This code should be put into the worksheet you want to paste content into from another workbook. When it runs it will prompt you to open a workbook to copy from ("Output" worksheet), it will then select the cells you specify in the code (JR:R3), paste them in starting at the next empty row of your initial workbook (finding the last row in column C in the "Master" worksheet), and then it will close & save the workbook you just copied from.

说明：应将此代码放入您要将其他工作簿中的内容粘贴到的工作表中。当它运行时，它会提示您打开要从中复制的工作簿（“输出”工作表），然后它会选择您在代码中指定的单元格（JR:R3），将它们粘贴到您初始的下一个空行开始工作簿（在“主”工作表的 C 列中找到最后一行），然后它将关闭并保存您刚刚从中复制的工作簿。

Sub CommandButton1_Click()

'Last cell in column
Dim WS As Worksheet
Dim LastCell As Range
Dim LastCellRowNumber As Long

Set WS = Worksheets("Master")
With WS
    Set LastCell = .Cells(.Rows.Count, "C").End(xlUp)
    LastCellRowNumber = LastCell.Row + 1
End With

Dim wb As Workbook, wb2 As Workbook
Dim vFile As Variant

'Set source workbook
Set wb = ActiveWorkbook

'Open the target workbook
vFile = Application.GetOpenFilename("Excel-files,*.xlsx", _
    1, "Select One File To Open", , False)

'if the user didn't select a file, exit sub
If TypeName(vFile) = "Boolean" Then Exit Sub
Workbooks.Open vFile

'Set selectedworkbook
Set wb2 = ActiveWorkbook

'Select cells to copy
wb2.Worksheets("Output").Range("J3:R3").Select
Selection.Copy

'Go back to original workbook you want to paste into
wb.Activate

'Paste starting at the last empty row
wb.Worksheets("Master").Range("C" & LastCellRowNumber).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True

'Close and save the workbook you copied from
wb2.Save
wb2.Close

End Sub

回答by engineersmnky

Have you tried this

你有没有试过这个

Selection.Copy
wb.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial xlPasteValues