rtod2
asked on
Update macro to remove macro
I have the code below designed as shown in the introductory comment. The only problem is that it copies in all my macros when what I wanted it to do was to strip the new file of all macros so that we have a nice clean sheet with no macros. Also, if possible, the tab created should take on the name of the tab from whence it came. Here is the code below:
Sub Make_Non_Macro_Sheet_From_Tab()
'This sub is intended to create a separate sheet from the active tab.'
'It will prompt for a file name with the current users desktop as the default,'
'remove empty tabs from the newly created sheet, and ensure that it is macro free.'
dtpath = CreateObject("wscript.shell").SpecialFolders("Desktop")
Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
wb.Worksheets.Add
ws.Cells.Copy ActiveSheet.Cells
ActiveSheet.Move
fn = Application.GetSaveAsFilename(dtpath)
If fn <> False Then
ActiveWorkbook.SaveAs fn & "xlsx"
Else
MsgBox "file not saved"
End If
End Sub
Assistance is greatly appreciated!ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER