复制范围并粘贴到新工作簿中-Java 学习之路

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

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

我的原始工作簿，让's call Book1. I'尝试创建一个新的工作簿，Book2，我将单元格A1：B10的值复制到 .

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

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

这给出了“RangeSpecial of Range class failed”错误 . 我尝试了以下修复，没有运气：

将'Workbooks( 2738676 ).Activate'添加到代码中
删除了PasteSpecial行中的额外参数
尝试'.Paste'而不是'.PasteSpecial'
已将'Selection.PasteSpecial'更改为'ActiveSheet.PasteSpecial'
显式引用复制范围，包括工作簿和工作表引用
首先创建新工作簿，然后执行复制，然后重新激活新工作簿并粘贴

上述解决方案都不起作用......现阶段的任何智慧都会感激不尽！

2 回答

这是你在尝试什么？我已经对代码进行了评论，因此您不应该在理解代码的作用时遇到任何问题 .

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

回复于 2024-05-02T16:10:43+08:00

这适合我 .

Private Sub CommandButton1_Click()
Dim newWB As Workbook, currentWB As Workbook
Dim newS As Worksheet, currentS As Worksheet

'Copy the data you need
Set currentWB = ThisWorkbook
Set currentS = currentWB .Sheets("Sheet1")
currentS .Range("A:M").Select
Selection.Copy

'Create a new file that will receive the data
Set newWB = Workbooks.Add
    With newWB
        Set newS = newWB.Sheets("Sheet1")
        newS.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        'Save in CSV
        Application.DisplayAlerts = False
        .SaveAs Filename:="C:\Temporary.csv", FileFormat:=xlCSV
        Application.DisplayAlerts = True
    End With
End Sub

回复于 2024-05-02T16:10:43+08:00

复制范围并粘贴到新工作簿中

2 回答

相关问题