[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Update macro to remove macro

Posted on 2011-05-03
2
Medium Priority
?
244 Views
Last Modified: 2012-08-13
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!


0
Comment
Question by:rtod2
2 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 35514073
Ted,

The macro above is supposed to not copy the macros over to the new sheet.

I have added a line for retaining the sheet name in the code below

Saqib
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
activesheet.name=ws.name
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

0
 

Author Closing Comment

by:rtod2
ID: 35514308
I might have read it wrong.  It's perfect now.  Thank you!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question