将数据从一个(关闭的)工作簿复制到另一个(已经打开的)工作簿 [英] Copying data from one (closed) workbook into another (already open) workbook

问题描述

我成功地成功创建了一个宏，该宏将一个(关闭的)工作簿中几列的内容复制到另一个已经打开的工作簿中.

I've managed to semi-successfully create a macro that copies the contents of a couple of columns in one (closed) workbook to another, already opened workbook.

问题是下面的代码导致最后一行始终显示每一列的N/A ...您知道我的代码中的错误是什么吗?我宁愿修复它，也不愿添加另一行代码来删除N/As.

Problem is that the code below results in the last row always showing N/A for each column... Do you know what the mistake in my code is? I would prefer to fix it than adding another line of code that removes the N/As.

这是代码(也我注意到使用ActiveWorkbook或ThisWorkbook会大大降低宏的速度...关闭自动计算以使其运行更快，但是如果您还有其他建议来简化代码，请告诉我).

Here's the code (also I noticed that using ActiveWorkbook or ThisWorkbook slows the macro down considerably... turned off auto calc to make it go faster, but please let me know if you have any further suggestions to simplify the code).

Sub DataFromClosedFile()

On Error GoTo ErrHandler

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

x是封闭的源工作簿y是当前活动的工作簿，我会将数据粘贴到其中

x is the closed, source workbook y is the current active workbook into which I will paste the data

Dim x As Workbook
Dim y As Workbook

Dim CA_TotalRows As Integer
Dim CA_Count As Integer

我认为带有"ThisWorkbook"的下一行代码会显着降低所有速度-不知道为什么，但是哦...

I think this next line of code with the 'ThisWorkbook' slows everything down significantly - not sure why, but oh well...

Set y = ThisWorkbook 'Could also have used ActiveWorkbook
Set x = Workbooks.Open("PATH", True, True)

接下来，计算需要复制的行数(我不想复制标题，因此为什么我要从第2行开始?

Next, count the number of rows that need to be copied (I don't want to copy the titles, hence why I start from row 2:

CA_TotalRows = x.Worksheets("August_2015_CA").Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Rows.Count + 1

此外，我只想从源数据手册中复制列A:B和E:H.因此，重复的公式.再次感谢您提供有关如何最大程度提高代码效率的建议！

Also, I only want to copy columns A:B and E:H from the source databook. Hence the repeated formula. Again, any suggestions on how to maximise the efficiency of the code are appreciated!

For CA_Count = 1 To CA_TotalRows
    y.Worksheets("Sheet3").Range("A1:B" & CA_Count).Formula = x.Worksheets("August_2015_CA").Range("A2:B" & CA_Count).Formula
Next CA_Count

For CA_Count = 1 To CA_TotalRows
    y.Worksheets("Sheet3").Range("C1:F" & CA_Count).Formula = x.Worksheets("August_2015_CA").Range("E2:H" & CA_Count).Formula
Next CA_Count

因此，在执行完最后一个代码之后的某个时刻，最后一行将被复制为一串N/As....如何避免呢?！

So at some point after that last code is executed, the last row then is copied as a bunch of N/As... for each of the columns. How to avoid that?!

x.Close False
Set x = Nothing

Application.Calculation = xlCalculationAutomatic

ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

推荐答案

错误是您开始从工作表"August_2015_CA"的第2行提取数据，然后从"Sheet1"的第1行粘贴数据.因此，最后一个循环引用"August_2015_CA"中的空单元格.而且，您一次又一次地粘贴相同的数据，这会降低代码的速度.

The error is that you start extracting data from row 2 in Worksheet "August_2015_CA" and paste it from row 1 in "Sheet1". Consequently, the last looping refers to empty cells in "August_2015_CA". Moreover, you are pasting the same data again and again, which slows down the code.

解决方案:

1)查找最后一行可能会容易一些:

1) Finding the last row might be a bit easier:

CA_TotalRows = x.Worksheets("August_2015_CA").UsedRange.Rows.Count

假设您在第1行中有数据

2)为什么不尝试将所有数据粘贴到一个中，这比循环要快得多:

2) Why don't you try to paste all the data in one - this would be much faster than looping:

y.Worksheets("Sheet3").Range("A1:B" & CA_TotalRows - 1).Formula = _ 
x.Worksheets("August_2015_CA").Range("A2:B" & CA_TotalRows).Formula

y.Worksheets("Sheet3").Range("C1:F" & CA_TotalRows - 1).Formula = _  
x.Worksheets("August_2015_CA").Range("E2:H" & CA_TotalRows).Formula

这篇关于将数据从一个(关闭的)工作簿复制到另一个(已经打开的)工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！