Hi
I have a workbook that has 3 macros and 3 worksheets. I created a saveas macro in order to transfer one worksheet to a different file and give it a filename that includes today's date. When I used the saveas macro, the worksheet was converted into a separate file in the correct destination and had the correct filename. But then, none of the 3 macros were present in this new file. Below is the code that I used. Can someone help me with figuring out why the macros didnt get transferred? Thanks!

Sub savemdrwork()

Thisdate = Range("F3").Value
If Thisdate = "" Then
MsgBox "Please Enter Today's Date", vbInformation
GoTo EndMacro
End If
Application.ScreenUpdating = False


ActiveSheet.Select
ActiveSheet.Copy
ThisFile = Range("F3").Value
ActiveSheet.SaveAs Filename:="E:\operator calibration files\mdr " & ThisFile & ".xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.ScreenUpdating = True

'EndMacro: Range("F3").Select
End Sub

Your macro is only saving the worksheet, not the workbook. You should delete the unwanted worksheets, then save the remaining workbook with the new name.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Paul..thank you!!! that worked! i was avoiding that strategy in the beginning because the unwanted macros remain in the saved workbook....but at the same time, if there is no code that can transfer macros of one worksheet, then this does end up being a wonderful option.
Thank you so much!!!

Hi missmr,

You can indeed transfer code from one workbook to another but, since the code's already in the workbook, it's more straightforward to do it this way. Any code attached to the worksheets you've deleted will also be deleted when you delete those sheets. All that might remain (apart from your saved worksheet's code) is code in userforms & other code modules, if you have them. Those, too, could be deleted via vba - but you'd have to give vba trusted access to the vba project object model - and that might not be advisable.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]