PDA

View Full Version : [SOLVED] Create copy of WB



winstonhuxle
08-12-2013, 04:36 AM
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
08-12-2013, 07:08 AM
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
08-16-2013, 01:23 AM
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