VBA从一个工作簿复制到另一个工作簿。

JOB: To copy RANGE from one WORKBOOK to ANOTHER (ANOTHER workbook exists and needs to be opened)

工作:从一个工作簿复制到另一个工作簿(存在另一个工作簿，需要打开)

Copy range:

复制范围:

`Worksheets("paste").Range("A2:BD500").SpecialCells(xlCellTypeVisible).Copy`

Open new file:

打开新文件:

Workbooks.Open Filename:="C:\Test\test.xlsx", WriteResPassword:="WriteFile"

工作簿。打开文件名:= " C:\ \测试。xlsx”,WriteResPassword:= " WriteFile "
Activate sheet and paste @RANGE A6

激活表格并粘贴@RANGE A6

Windows("test.xlsx").Activate Selection.Range("A6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False

Windows(“test.xlsx”)。激活Selection.Range(A6)。特殊的粘贴:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False，转置:=False。

PROBLEM: It doesn't paste in A6!!!! It goes in whatever cell!!!!

问题:它不会粘贴到A6!!!它会进入任何单元格!!!

2 个解决方案

#1

If your current selection in the test.xlsx workbook opens to D5 then using Selection.Range("A6") references D10, not A6.

如果您当前的选择在测试中。xlsx工作簿打开到D5，然后使用Selection.Range(“A6”)引用D10，而不是A6。

Dim wb As Workbook

Set wb = Workbooks.Open(Filename:="C:\Test\test.xlsx", WriteResPassword:="WriteFile")

With Worksheets("paste")
    .Range("A2:BD500").SpecialCells(xlCellTypeVisible).Copy
    wb.Worksheets(1).Cells(6, "A").PasteSpecial xlPasteValues
End With

See How to avoid using Select in Excel VBA macros. You should never rely on a static cell or cells being the current selection when opening a workbook.

查看如何避免在Excel VBA宏中使用Select。在打开工作簿时，不应该依赖静态单元格或单元格作为当前的选择。

#2

A simple way to copy a specific range between workbooks:

在工作簿之间复制特定范围的简单方法:

Workbooks(source).Worksheets("Sheet1").Range("A2:BD500").Copy _
    Workbooks(destination).Worksheets("Sheet1").Range("A6")

#1