Link to home
Start Free TrialLog in
Avatar of rtod2
rtod2Flag for United States of America

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

Open in new window

Assistance is greatly appreciated!


ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rtod2

ASKER

I might have read it wrong.  It's perfect now.  Thank you!