VBA-如何将Excel中的行从一个工作簿复制到另一个工作簿? [英] VBA - How to copy row in Excel from one workbook to another?

问题描述

尽管我通过与问题相同的方式查看过许多帖子，但没有一个答案能满足我的需求.如果您可以将我链接到某个链接，我会很乐意阅读.

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.

我有一本带有工作表的工作簿.为简单起见，假设我的工作簿有一个工作表.在我的名为"Sheet1"的工作表中，单元格A1至A4中有数据.

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.

我想让我的VBA代码执行以下操作:

What I want my VBA code to do is:

1. 将工作簿"A"的第1行(或具体地说是单元格A1到A4)复制到范围变量"myRange"中
2. 创建一个新工作簿，我们将此工作簿称为"B"
3. 给工作簿'B的默认工作表1"重新命名为测试名称"
4. 打开工作簿"B"(尽管我意识到VBA代码"Workbooks.Add"会打开一本新书，因此由于Workbooks.Add涵盖了第2点和第3点的一半，因此此步骤可能是多余的)
5. 将"myRange"粘贴到工作簿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:

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

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

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?

任何帮助将不胜感激.

谢谢， QF

推荐答案

您可以直接执行此操作，而不用复制粘贴上面提到的方法.这也会否定使用变量.

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")

编辑

我刚刚发现您的代码有错误.

I just noticed an error with your code.

newBook = Workbooks.Add

此行会给您一个错误，因为您必须使用Set

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

您的代码可以写为

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

更多编辑

详细说明SET

我建议您看这篇文章.

LINK:工作表无法正常工作

这篇关于VBA-如何将Excel中的行从一个工作簿复制到另一个工作簿?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！