Create copy of WB

[winstonhuxle]

Hello,

Guess it easy, but I couldn't find it. How could I create a copy of active wb, usual way with SaveCopyAs and then open is not very effective for me, as I work with big files, so the saving took a lot of time. Thanks in advance.

[Kenneth Hobs]

In this method, the 2nd input parameter can also be just a different path. Note that FileCopy will error so I used an FSO method. You will need to save the current file first if you want any changes saved.

Sub Test_CopyActiveWorkbook()
  CopyActiveWorkbook ThisWorkbook, "c:\temp\" & ThisWorkbook.Name
End Sub

Sub CopyActiveWorkbook(aWB As Workbook, theFullPathName As String)
  'FileCopy aWB.FullName, theFullPathName 'Errors
  CopyFile aWB.FullName, theFullPathName
End Sub

Sub CopyFile(filespec, Destination)
    Dim fs, f, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    fs.CopyFile filespec, Destination
End Sub

[winstonhuxle]

Hi Kenneth, thanks this way I found out. It works quite well

With NewBook
        .SaveAs Filename:="C:\" & "copy") & ".xls"
        Workbooks("file.xls").Activate
        ThisWorkbook.Sheets.Copy After:=Workbooks("copy".xls").Worksheets(Workbooks("copy.xls").Worksheets.Count)
    End With