How to copy the contents of the active sheet to a new workbook?

Question

I'm trying to copy the contents of the active sheet to a new workbook.

Sub new_workbook()

    Dim ExtBk As Workbook
    Dim ExtFile As String

    Columns("A:N").Copy

    Workbooks.Add.SaveAs Filename:="output.xls"
    ExtFile = ThisWorkbook.Path & "\output.xls"

    Set ExtBk = Workbooks(Dir(ExtFile))
    ExtBk.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

    Application.DisplayAlerts = False
    ExtBk.Save
    Application.DisplayAlerts = True

End Sub

I'm getting an error at the PasteSpecial line with the error specified at the subject. I'm a bit confused since this works if I direct it to the source workbook.

Maybe I need to use Windows(output.xls)?

What's the file format of the source workbook? You might be trying to paste too many rows if you're going from an xlsx to an xls — Tim Williams, Jun 24 '13 at 18:15
hmm. It is an xlsx. Would it work if I tried copying it to an xlsx instead? — yatici, Jun 24 '13 at 18:18
so if I try with the xlsx format the I get an error :Application-defined or object defined error — yatici, Jun 24 '13 at 18:31
The PasteSpecial method simply does not work in my experience. I have tried it many times in different macros and it never works. I recommend using a plain cut or copy and plain paste, then applying any formatting. — SendETHToThisAddress, May 15 '19 at 01:48

score 17 · Accepted Answer · answered Jun 24 '13 at 18:40

17

Don't use Copy method at all if you're only concerned with saving the Values.

Sub new_workbook()
Dim wbMe As Workbook: Set wbMe = ThisWorkbook
Dim ws As Worksheet: Set ws = wbMe.ActiveSheet
Dim ExtBk As Workbook

Set ExtBk = Workbooks.Add
ExtBk.SaveAs Filename:=wbMe.Path & "\output.xls"

ExtBk.Worksheets("Sheet1").Range("A:N").Value = ws.Range("A:N").Value

Application.DisplayAlerts = False
ExtBk.Save
Application.DisplayAlerts = True

End Sub

Note: this will fail (and so will your code, previously) if your ThisWorkbook is unsaved.

answered Jun 24 '13 at 18:40

David Zemens

53,033
11
81
130

1

Good call. It is often unnecessary to populate (pollute) the clipboard. – Andy G Jun 24 '13 at 18:43
yea gave me the same error when trying to set the values identical. – yatici Jun 24 '13 at 19:13
@yatici You can't possibly get the "same error" because this code is not invoking the `PasteSpecial` method. So, what error are you getting? – David Zemens Jun 24 '13 at 19:16
aha nevermind seems like it liked it. I had forgot to comment out the pastespecial. This is nice. Seems slow but definitely handy – yatici Jun 24 '13 at 19:20
The speed is probably a function of the fact that you're copying the *entire* column, so, 12 columns * 1048576 rows. Lots of data. If you were to revise the range to the smaller range of data which you actually need to copy (for example, `Range("A1:N3502")` etc.), performance should be much much faster. Cheers. – David Zemens Jun 24 '13 at 19:23

score 2 · Answer 2 · edited Sep 20 '19 at 20:11

I made it work:

Sub cp2NewWb()
    Dim ExtFile As String
    ExtFile = ThisWorkbook.Path & "output.xls"
    Workbooks.Add.SaveAs Filename:="output.xls"

    Windows("test1.xlsm").Activate
    Range("A1:AA100").Copy
    Windows("output.xls").Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Worksheets(Worksheets.Count).Columns("A:AA").EntireColumn.AutoFit
    Range("A1").Select

    Windows("test1.xlsm").Activate
    Application.CutCopyMode = False
    Range("A1").Select
End Sub

I need to do it between activating windows or it doesn't work.

Andy G · Answer 3 · 2013-06-24T18:35:53.823

1

If you are copying the entire area, then copy the worksheets:

Worksheets("Sheet1").Copy Workbooks(2).Worksheets(1)

If it copies a couple of columns that you don't need then you could delete this afterwards.

If you are copying from .xlsx to .xls then you'll need to use Copy/Paste:

Worksheets("Sheet1").UsedRange.Copy Workbooks(2).Worksheets(1).Range("A1")

If pasting values is required:

Workbooks(2).Worksheets(1).UsedRange.Copy
Workbooks(2).Worksheets(1).Range("A1").PasteSpecial xlPasteValues

Be aware that UsedRange won't start from A1 unless this cell has some content. In which case, you'll have to define a Range object that starts at A1 and extends to the last used cell.

edited Jun 24 '13 at 18:35

answered Jun 24 '13 at 18:27

Andy G

19,232
5
47
69

I am ok with copying the whole sheet but I need paste special as the original workbook if formulas and I want the results to be saved in this newly generated workbook. – yatici Jun 24 '13 at 18:30
@yatici I've added a further suggestion to my answer. – Andy G Jun 24 '13 at 18:37

score 0 · Answer 4 · answered Jun 24 '13 at 18:23

Private Sub ExceltoExcel()
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    'Input Data
     Sheets("Sheet1").Cells(1, 1).Select
     col = Sheets("Sheet1").Cells(2, 2)
     Dim exlApp As Excel.Application
     Dim ExtBk As Excel.Workbook
     Dim exlWs As Excel.Worksheet
     ExtFile = ThisWorkbook.Path & "\output.xls"
     Set exlApp = CreateObject("Excel.Application")
     Set ExtBk = exlApp.Workbooks.Open(ExtFile)
     Set exlWs = exlWb.Sheets("Sheet1")
     ExtBk.Activate
     exlWs.Cells(2, 2) = col
     'Output Data
     exlWs.Range("A1").Select
     exlWb.Close savechanges:=True
     Set ecxlWs = Nothing
     Set exlWb = Nothing
     exlApp.Quit
     Set exlApp = Nothing
     Application.EnableEvents = True
     Application.DisplayAlerts = True
End Sub

How to copy the contents of the active sheet to a new workbook?

4 Answers4

Linked