How to copy the contents of the active sheet to a new workbook?
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.
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.