Making a copy of a macro-enabled excel file

mcorydon

New Member
Joined
Jan 6, 2014
Messages
16
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

BIGTONE559

Active Member
Joined
Apr 20, 2011
Messages
336
Code:
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

Well-known Member
Joined
Feb 22, 2013
Messages
1,161
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.

Code:
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

Active Member
Joined
Apr 20, 2011
Messages
336
AHA Red! you're absolutelyi right. . . (was answering on the fly). . . RED's addition should fare just fine.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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.

Code:
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
 
Last edited:

Forum statistics

Threads
1,137,296
Messages
5,680,672
Members
419,924
Latest member
Dhamodharan992

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top