vba 在工作簿之间复制单元格

Copy cells between workbooks

提问by trunks

Could someone please help me with some VBA code.

有人可以帮我一些 VBA 代码。

I am trying to copy 2 ranges of cells between workbooks (both workbooks should be created beforehand as i don't want the code to create a new workbook on the fly).

我正在尝试在工作簿之间复制 2 个单元格范围（应该事先创建两个工作簿，因为我不希望代码动态创建新工作簿）。

Firstly I need to copy these ranges- From 'Sheet 3' of booka.xls, Range: Cell H5 to the last row in column H with data copy this to 'Sheet 1' of bookb.xls, starting in Cell B2 for as many cells down in the B column

首先，我需要复制这些范围 - 从 booka.xls 的“表 3”，范围：单元格 H5 到 H 列中的最后一行，并将数据复制到 bookb.xls 的“表 1”，从单元格 B2 开始B列向下的单元格

Secondly I need to copy these ranges- From 'Sheet 3' of booka.xls, Range: Cell K5 to the last row in column K with data copy this to 'Sheet 1' of bookb.xls, starting in Cell D2 for as many cells down in the D column

其次，我需要复制这些范围 - 从 booka.xls 的“表 3”，范围：单元格 K5 到 K 列中的最后一行，并将数据复制到 bookb.xls 的“表 1”，从单元格 D2 开始D列中向下的单元格

Here is what I have so far:

这是我到目前为止所拥有的：

 Sub CopyDataBetweenBooks()

Dim iRow        As Long
    Dim wksFr       As Worksheet
    Dim wksTo       As Worksheet

    wksFr = "C:\booka.xls"
    wksTo = "C:\bookb.xls"

    Set wksFrom = Workbooks(wksFr).Worksheets("Sheet 3")
    Set wksTo = Workbooks(wksTo).Worksheets("Sheet 1")

    With wksFrom
        For iRow = 1 To 100
            .Range(.Cells(iRow, 8), .Cells(iRow, 9)).Copy wksTo.Cells(iRow, 8)
        Next iRow
    End With

End Sub

回答by jonsca

Here's an example of how to do one of the columns:

以下是如何执行其中一列的示例：

Option Explicit
Sub CopyCells()
    Dim wkbkorigin As Workbook
    Dim wkbkdestination As Workbook
    Dim originsheet As Worksheet
    Dim destsheet As Worksheet
    Dim lastrow As Integer
    Set wkbkorigin = Workbooks.Open("booka.xlsm")
    Set wkbkdestination = Workbooks.Open("bookb.xlsm")
    Set originsheet = wkbkorigin.Worksheets("Sheet3")
    Set destsheet = wkbkdestination.Worksheets("Sheet1")
    lastrow = originsheet.Range("H5").End(xlDown).Row
    originsheet.Range("H5:H" & lastrow).Copy  'I corrected the ranges, as I had the src
    destsheet.Range("B2:B" & (2 + lastrow)).PasteSpecial 'and destination ranges reversed
End Sub

As you have stated in the comments, this code above will not work for ranges with spaces, so substitute in the code below for the lastrowline:

正如您在评论中所述，上面的代码不适用于带空格的范围，因此请在下面的代码中替换该lastrow行：

lastrow = originsheet.range("H65536").End(xlUp).Row

Now ideally, you could make this into a subroutine that took in an origin workbook name, worksheet name/number, and range, as well as a destination workbook name, worksheet name/number, and range. Then you wouldn't have to repeat some of the code.

现在理想情况下，您可以将其变成一个子例程，该子例程接收源工作簿名称、工作表名称/编号和范围，以及目标工作簿名称、工作表名称/编号和范围。这样您就不必重复某些代码了。

回答by shahkalpesh

Assuming you have the reference to wksFromand wksTo, here is what the code should be

假设你有对wksFromand的引用wksTo，这里是代码应该是什么

wksFrom.Range(wksFrom.Range("H5"), wksFrom.Range("H5").End(xlDown)).Copy wksTo.Range("B2")
wksFrom.Range(wksFrom.Range("K5"), wksFrom.Range("K5").End(xlDown)).Copy wksTo.Range("D2")

回答by Lopsided

You can use special cells like Jonsca has suggested. However, I usually just loop through the cells. I find it gives me more control over what exactly I am copying. There is a very small effect on performance. However, I feel that in the office place, making sure the data is accurate and complete is the priority. I wrote a response to a question similar to this one that can be found here:

您可以使用 Jonsca 建议的特殊单元格。但是，我通常只是遍历单元格。我发现它让我可以更好地控制我正在复制的内容。对性能的影响非常小。但是，我觉得在办公场所，确保数据准确和完整是重中之重。我写了一个与此类似的问题的回复，可在此处找到：

• StackOverflow - Copying Cells in VBA for Beginners

• StackOverflow - 在 VBA 中为初学者复制单元格

There is also a small demonstration by iDevelop on how to use special cells for the same purpose. I think that it will help you. Good luck!

iDevelop 还有一个关于如何将特殊单元用于相同目的的小型演示。我认为它会帮助你。祝你好运！

Update

更新

In response to...

作为回应...

good start but it doesn't copy anything after the first blank cell – trunks Jun 9 '11 at 5:08

良好的开端，但在第一个空白单元格之后它不会复制任何内容 – 中继 2011 年 6 月 9 日 5:08

I just wanted to add that the tutorial in the link above will address the issue brought up in your comment. Instead of using the .End(xlDown)method, loop through the cells until you reach the last row, which you retrieve using .UsedRange.Rows.Count.

我只是想补充一点，上面链接中的教程将解决您评论中提出的问题。不要使用该.End(xlDown)方法，而是遍历单元格，直到到达最后一行，您可以使用.UsedRange.Rows.Count.