Making a copy of a macro-enabled excel file

[mcorydon]

Is there a way to make a copy of a macro-enabled excel file into a new excel file where all of the original values and formatting matches the original (ie. if a a data field is a vlookup formula I want the shown data to be copied not the formual itself)???

Any help would be greatly appreiciate and save me a lot of time

Matt

 

[BIGTONE559]

Sub Copy()
Dim filepath As String
Dim filecopy As String
'Sub filepath for actual directory of where you'd like the file saved
filepath = "C:\Users\YOU\My Documents\"
'sub filecopy for the name of the file that you'd like to create
filecopy = "Copy of File.xlsb"
ActiveWorkbook.SaveAs filepath & filecopy, FileFormat:=50


End Sub

 

[Redwolfx]

Correct me if I'm wrong Bigtone, but that is going to create an exact copy of the file. Since they are looking for Values not Formulas, perhaps adding.

Sub CopyValues
Dim filepath As String
Dim filecopy As String
Dim WS as Worksheet

For Each WS in ThisWorkbook.Worksheets 
With WS.Cells 
.Value = .Value 
End With
Next WS

'Sub filepath for actual directory of where you'd like the file saved
filepath = "C:\Users\YOU\My Documents\"
'sub filecopy for the name of the file that you'd like to create
filecopy = "Copy of File.xlsb"
ActiveWorkbook.SaveAs filepath & filecopy, FileFormat:=50

End Sub

</PRE>

 

[BIGTONE559]

AHA Red! you're absolutelyi right. . . (was answering on the fly). . . RED's addition should fare just fine.

 

[xenou]

Also be careful, as you don't want to lose unsaved changes (but you don't want to save the hard-value version under the original name either). I would do the saving first, so that if it fails you can abort without making the changes to the worksheet to hard code the values. Even more robust would be to make the copy first (SaveCopyAs method), then open the copy, so that you leave the original completely out of it and immune from harm.

Sub CopyValues
Dim filepath As String
Dim filecopy As String
Dim WS as Worksheet


On Error GoTo Err_Exit:

'//Save unsaved changes
Thisworkbook.Save

'Sub filepath for actual directory of where you'd like the file saved
filepath = "C:\Users\YOU\My Documents\"
'sub filecopy for the name of the file that you'd like to create
filecopy = "Copy of File.xlsb"
ActiveWorkbook.SaveAs filepath & filecopy, FileFormat:=50


For Each WS in ThisWorkbook.Worksheets 
    With WS.Cells 
        .Value = .Value 
    End With
Next WS

Exit Sub

Err_Exit:
Msgbox "Warning: An Unhandled Error Occurred."

End Sub