VBA Excel 宏将一列复制到另一工作簿中

Question

我有 2 个工作簿，正在尝试找到一种将列从 wb1 复制到 wb2 的方法。我知道我可以复制/粘贴，但我的想法是制作一些东西，以便我的老板可以单击宏并填充所有内容。

我一直在尝试在另一个问题中遇到的代码:

Sub Import()

Dim sourceColumn As Range
Dim destColumn As Range

Set sourceColumn = Workbooks("C:\Documents and Settings\********\My Documents\*********.xlsm").Worksheets(2).Columns("BL")
Set destColumn = Workbooks("C:\Documents and Settings\********\My Documents\*********.xlsm").Worksheets(2).Columns("A")

sourceColumn.Copy Destination = destColumn

End Sub

当我运行此命令时，出现“下标超出范围”错误。

源列包含依赖于其他列的公式，并且目标列为空，但即使我在具有小数字的虚拟工作簿上运行此公式，我也遇到了相同的错误。

我在这里缺少一些 super 基本的东西吗？

Answer 1

编辑:刚刚意识到您的那段代码可能缺少什么。里面加一个“:”!更改为 destination:=Workbooks(".... ，它应该可以正常工作。

额外详细信息:使用函数参数时，您必须添加“:”，以便向计算机指定您不是在计算相等性，而是在进行参数赋值。

<小时/>

(旧的、华而不实的答案)我认为这就是你想要做的；这个脚本可能会做你想要的。但是，样式不会被保留。

Sub yourSub()

Application.ScreenUpdating = False 'Disables "Screen flashing" between 2 workbooks

Dim colA As Integer, colB As Integer
Dim rowA As Integer, rowB As Integer
Dim wbA As Workbook, wbB As Workbook

Set wbA = ThisWorkbook
Set wbB = Workbooks.Open("C:/YourFilePath/YourFile.xls")

colA = 1 'Replace "1" with the number of the column FROM which you're copying
colB = 1 'Replace "1" with the number of the column TO which you're copying

rowA = 1 'Replace "1" with the number of the starting row of the column FROM which you're copying
rowB = 1 'Replace "1" with the number of the row of the column TO which you're copying

wbA.Activate
lastA = Cells(Rows.Count, colA).End(xlUp).Row 'This finds the last row of the data of the column FROM which you're copying
For x = rowA To lastA 'Loops through all the rows of A
    wbA.Activate
    yourData = Cells(x, colA)
    wbB.Activate
    Cells(rowB, colB) = yourData
    rowB = rowB + 1 'Increments the current line of destination workbook
Next x 'Skips to next row

Application.ScreenUpdating = True 'Re-enables Screen Updating

End Sub

我还没有时间测试这个。会尽快做的。