Copying range and pasting into new workbook

Question

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..?

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.

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

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

• 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!

Answer 1

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

Answer 2

This works for me.

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