VBA - 如何将 Excel 中的行从一个工作簿复制到另一个？

VBA - How to copy row in Excel from one workbook to another?

提问by qwerty_face

Despite many posts I have looked through being of along the same lines as my question, none of the answers satisfy what I am looking for. If you can link me to one I'd gladly read it.

尽管我浏览了许多与我的问题相同的帖子，但没有一个答案满足我的要求。如果你能把我链接到一个我很乐意阅读它。

I have a workbook with worksheets. For simplicity, let's say my workbook has a worksheet. And in my worksheet which is called "Sheet1", there is data in cells A1 to A4.

我有一个带有工作表的工作簿。为简单起见，假设我的工作簿有一个工作表。在我的名为“Sheet1”的工作表中，单元格 A1 到 A4 中有数据。

What I want my VBA code to do is:

我希望我的 VBA 代码做的是：

1. Copy row 1 (or specifically cells A1 to A4) of Workbook 'A' into Range variable 'myRange'
2. Create a new workbook, let's call this one Workbook 'B'
3. Give Workbook 'B's default "sheet1" a new name to "Test Name"
4. Open Workbook 'B' (though I realise that VBA code "Workbooks.Add" opens a new book so this step may be redundant since Workbooks.Add covers half of point 2 and 3)
5. Paste 'myRange' into first row of 'Workbook B'
6. Save 'Workbook B' with name "Test Book" and a timestamp enclosed in square brackets. The file must also be of the file extension "xls"
7. Close 'Workbook B' and return to 'Workbook A'

1. 将工作簿“A”的第 1 行（或特别是单元格 A1 到 A4）复制到范围变量“myRange”中
2. 创建一个新的工作簿，我们称这个工作簿为“B”
3. 将工作簿“B”的默认“sheet1”命名为“Test Name”
4. 打开工作簿“B”（虽然我意识到 VBA 代码“Workbooks.Add”会打开一本新书，因此这一步可能是多余的，因为 Workbooks.Add 涵盖了第 2 点和第 3 点的一半）
5. 将“myRange”粘贴到“Workbook B”的第一行
6. 使用名称“Test Book”和方括号括起来的时间戳保存“Workbook B”。该文件还必须是文件扩展名“xls”
7. 关闭“工作簿 B”并返回“工作簿 A”

What I have so far is this:

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

Sub OpenAndSaveNewBook()
    'Declarations
    Dim MyBook As String
    Dim MyRange As Range
    Dim newBook As Workbook

    'Get name of current wb
    MyBook = ThisWorkbook.Name
    Set MyRange = MyBook.Sheets("Sheet1").Range("A1,F1")

    'Create/Open new wb
    newBook = Workbooks.Add

    'Save new wb with XLS extension
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "/" & "TEST-BOOK", _
                            FileFormat:=xlNormal, CreateBackup:=False

    '===NOTE: BEFORE THE FOLLOWING RUNS I NEED TO PERFORM ACTIONS ON CELLS VIA VBA ON
    '===WORKBOOK 'A'. DOES THE NEWLY CREATE WORKBOOK BECOME THE PRIMARY/ACTIVE WORKBOOK
    '===? AND SO THEN DO I NEED TO ACTIVATE WORKBOOK 'A'? 
    ActiveWorkbook.Close savechanges:=True

    'Return focus to workbook 'a'
    MyBook.Activate
End Sub

As you can see, I am lacking the code that will handle:

如您所见，我缺少可以处理的代码：

• the pasting of my copied data to the new workbook
• the changing of the new workbook's sheet1 name to something else
• adding a timestamp to the filename string on save

• 将我复制的数据粘贴到新工作簿
• 将新工作簿的 sheet1 名称更改为其他名称
• 在保存时向文件名字符串添加时间戳

Lastly, I have included a question in my code as I think I may have a misunderstanding of the ActiveWorkbook method. AFAIK when the code "Workbooks.Add" runs this becomes the Active Workbook, i.e. one with the focus. Does this effect how the VBA code running on Workbook 'A'? Does this mean that if I wanted to add code to manipulate cells of Workbook 'A' then I would need to use "MyBook.Activate" where 'MyBook' holds the string of Workbook 'A's actual title?

最后，我在我的代码中包含了一个问题，因为我认为我可能对 ActiveWorkbook 方法有误解。AFAIK 当代码“Workbooks.Add”运行时，这将成为活动工作簿，即具有焦点的工作簿。这是否会影响在工作簿“A”上运行的 VBA 代码？这是否意味着如果我想添加代码来操作工作簿“A”的单元格，那么我需要使用“MyBook.Activate”，其中“MyBook”保存工作簿“A”的实际标题字符串？

Any help will be greatly appreciated.

任何帮助将不胜感激。

Thanks, QF

谢谢，QF

回答by Siddharth Rout

Instead of copy pasting the way you mentioned above, you can directly do this. This will also negate the use of a variable.

您可以直接执行此操作，而不是像上面提到的那样复制粘贴。这也将否定变量的使用。

MyBook.Sheets("Sheet1").Rows("1:4").copy _
newBook.Sheets("Sheet1").Rows("1")

EDIT

编辑

I just noticed an error with your code.

我刚刚注意到您的代码有错误。

newBook = Workbooks.Add

This line will give you an error as you have to use Set

这一行会给你一个错误，因为你必须使用 Set

Your code can be written as

你的代码可以写成

Option Explicit

Sub OpenAndSaveNewBook()
    Dim MyBook As Workbook, newBook As Workbook
    Dim FileNm As String

    Set MyBook = ThisWorkbook

    FileNm = ThisWorkbook.Path & "\" & "TEST-BOOK.xls"
    Set newBook = Workbooks.Add

    With newBook
        MyBook.Sheets("Sheet1").Rows("1:4").Copy .Sheets("Sheet1").Rows("1")

        'Save new wb with XLS extension
        .SaveAs Filename:=FileNm, FileFormat:=xlNormal, CreateBackup:=False

        .Close Savechanges:=False
    End With
End Sub

MORE EDIT

更多编辑

Elaborating on the use of SET

详细说明使用 SET

I would recommend you to see this post.

我建议你看看这篇文章。

LINK: Worksheets does not work

链接：工作表不起作用

回答by e100

Avoid references to ActiveWorkbook in favour of explicit references wherever possible.

尽可能避免引用 ActiveWorkbook 以支持显式引用。

As you've found, it can be confusing knowing what's currently active, and you do not need to activate a workbook to manipulate it.

正如您所发现的，了解当前处于活动状态的内容可能会令人困惑，而且您无需激活工作簿即可对其进行操作。

So you should be using

所以你应该使用

newBook.SaveAs... 
newBook.Close...

Recorded macros tend to activate workbooks in order to work on them, but that's because that's the way a human who recorded them works! All activation really does is change focus.

录制的宏往往会激活工作簿以对其进行处理，但那是因为录制宏的人就是这样工作的！所有激活真正做的是改变焦点。

The same applies to making selections and then manipulating the current selection; it's not necessary in VBA and tends to be slower than direct manipulation.

这同样适用于进行选择然后操作当前选择；它在 VBA 中不是必需的，并且往往比直接操作慢。

回答by RavenM

The awesome thing about Excel is the 'Record Macro' function. I started recording a macro and just followed the steps you outlined, then made a few minor modifications to the code that Excel provided as the recorded macro:

Excel 的绝妙之处在于“记录宏”功能。我开始录制一个宏，只是按照您概述的步骤进行操作，然后对 Excel 提供的作为录制宏的代码进行了一些小的修改：

Range("A1:F1").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Sheets("Sheet1").Name = "Test Name"
Application.CutCopyMode = False
myNewFileName = myPath & myTestName & "_" & Date & ".xls"
ActiveWorkbook.SaveAs Filename:=myNewFileName _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

The Datefunction returns the current system date. It is important to note that the square brackets that you wanted are not valid filename characters; Excel will throw an error if you try to use those.

该Date函数返回当前系统日期。请务必注意，您想要的方括号不是有效的文件名字符；如果您尝试使用它们，Excel 将引发错误。

回答by Steven

Turn the macro-recorders on; carefully execute the steps you want; stop the recorder; "edit" the macro generated. Fix as you need to make the program you intend, e.g., to parameterize it.

打开宏记录器；仔细执行你想要的步骤；停止录音机；“编辑”生成的宏。根据需要修复您想要的程序，例如，对其进行参数化。