复制范围并粘贴到新的工作簿中 [英] Copying range and pasting into new workbook

问题描述

这应该很简单，但是我已经拖了几个小时的论坛和答案，找不到答案，没有运气，所以我（不情愿地）创建一个我自己的问题。

This should be really simple, but I've been trawling forums and SO answers for hours to find the answer with no luck, so am (reluctantly) creating a question of my own.

我想要做的只是创建一个新的工作簿，并将一个范围从另一个工作簿粘贴到该工作簿中。听起来很简单..？

What I'm trying to do is simply create a new workbook, and paste a range from another workbook into that workbook. Sounds simple..?

我的原始工作簿，让我们来打电话给Book1。我正在尝试创建一个新的工作簿Book2，我将复制单元格A1：B10的值。

My original workbook, let's call Book1. I'm trying to create a new workbook, Book2, which I will copy the values of cells A1:B10 to.

这是一个版本我的代码（从Book1开始）：

Here's one version of my code (starting with Book1 open):

Range("A1:B10").Copy
Set NewBook = Workbooks.Add
    With NewBook
        .SaveAs Filename:="Book2.xls"
    End With
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

这给出了PasteSpecial of Range类失败错误。我尝试了以下修复，没有运气：

This gives a "PasteSpecial of Range class failed" error. I have tried the following fixes with no luck:

• 添加工作簿（Book2.xls）。激活' li>
  
• 删除了PasteSpecial行中的额外参数


• 尝试'.Paste'而不是'.PasteSpecial'


• 将Selection.PasteSpecial更改为ActiveSheet.PasteSpecial


• 明确引用复制范围，包括工作簿和工作表参考


• 创建新工作簿首先，然后执行副本，然后重新启动新的工作簿并粘贴

• added 'Workbooks("Book2.xls").Activate' to the code
• removed the extra arguments in the PasteSpecial line
• tried '.Paste' instead of '.PasteSpecial'
• changed 'Selection.PasteSpecial' to 'ActiveSheet.PasteSpecial'
• explicitly referencing the copy range, including the workbook and sheet reference
• creating the new workbook first, then performing the copy, before reactivating the new workbook and pasting

上述解决方案中没有一个工作...这个任何智慧这个阶段会非常感激的！

None of the above solutions work... any wisdom at this stage would be gratefully received!

推荐答案

我已经评论过代码，所以你不应该对代码做什么感兴趣。

Is this what you are trying? I have commented the code so that you shouldn't have any problem understanding what the code does.

Option Explicit

Sub Sample()
    Dim wbI As Workbook, wbO As Workbook
    Dim wsI As Worksheet, wsO As Worksheet

    '~~> Source/Input Workbook
    Set wbI = ThisWorkbook
    '~~> Set the relevant sheet from where you want to copy
    Set wsI = wbI.Sheets("Sheet1")

    '~~> Destination/Output Workbook
    Set wbO = Workbooks.Add

    With wbO
        '~~> Set the relevant sheet to where you want to paste
        Set wsO = wbO.Sheets("Sheet1")

        '~~>. Save the file
        .SaveAs Filename:="C:\Book2.xls", FileFormat:=56

        '~~> Copy the range
        wsI.Range("A1:B10").Copy

        '~~> Paste it in say Cell A1. Change as applicable
        wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    End With
End Sub

这篇关于复制范围并粘贴到新的工作簿中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！