?
Solved

Create duplicate sheet - macro needed

Posted on 2011-05-02
15
Medium Priority
?
415 Views
Last Modified: 2012-05-11
I am using Excel 2010.

I need a macro that will, with a single (1) click...

1) Make a duplicate file from the active sheet.
2) Prompt for a new name for that file.
3) Ensure that the new file has only one tab and is stripped of any self-containing macros.

Is there any way to accomplish this?
0
Comment
Question by:rtod2
  • 8
  • 5
  • 2
15 Comments
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 35509100
Here is a sub that will do what you need. You can create a button on your workbook and attach it to this macro so it can be run at anytime

Michael
Sub test()
   Dim current As Workbook, created As Workbook
   Dim sht As Worksheet
   Dim fn As String
   
   
   Set current = ActiveWorkbook
   Set sht = current.ActiveSheet
      
   sht.Copy
     
   fn = Application.GetSaveAsFilename
   If fn = False Then
      Application.DisplayAlerts = False
      ActiveWorkbook.Close
      Application.DisplayAlerts = True
      Exit Sub
   End If
   
   ActiveSheet.SaveAs fileName:=fn & ".xlsx"
   
End Sub

Open in new window

Book1.xlsm
0
 

Author Comment

by:rtod2
ID: 35509244
Awesome!  Here is a video of my implementation.

http://screencast.com/t/s5WL3l2LKo

It created an error for me.  I also noticed prior to making the video, that I had to tell it to go to the desktop.  I'd prefer that it find the active desktop by default if possible.

Thanks for your help with this Michael!!
0
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 35509873
Sorry about that small error now corrected and it will automatically open the file dialog box in the current users desktop. This should work fine on any version of windows

Michael


Sub test()

   Dim sht As Worksheet
   Dim fn As String
   Dim wss As Object
   
   Set sht = current.ActiveSheet
      
   sht.Copy
   
   Set wss = CreateObject("WScript.Shell")
   ChDir wss.specialfolders("Desktop")
   Set wss = Nothing
   
   fn = Application.GetSaveAsFilename
   If fn = "False" Then
      Application.DisplayAlerts = False
      ActiveWorkbook.Close
      Application.DisplayAlerts = True
      Exit Sub
   End If
   
   ActiveSheet.SaveAs fileName:=fn & ".xlsx"
   
End Sub

Open in new window

Book1.xlsm
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35509919
Michael,

If the sheet has some macros they would be copied over as well. Maybe you need to do something like this.

Also on 2003  the returned file name contained a period so I omitted the period from the saveas statement.

Saqib
Sub ws2newfile()
Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
wb.Worksheets.Add
ws.Cells.Copy ActiveSheet.Cells
ActiveSheet.Move
fn = Application.GetSaveAsFilename
If fn <> False Then
ActiveWorkbook.SaveAs fn & "xls"
Else
MsgBox "file not saved"
End If
End Sub

Open in new window

0
 

Author Comment

by:rtod2
ID: 35509927
Thanks.  I got the error even earlier this time.  Here is the video http://screencast.com/t/qW92TQyb

I am very grateful for your help.
0
 

Author Comment

by:rtod2
ID: 35510043
Thanks to you both!!

ssaqibh,  
I have this video on what I got with that one http://screencast.com/t/0797UabYXbSh .

I also noticed that it hangs onto the local macros as well.  
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35510048
Ted, did you try mine?
0
 

Author Comment

by:rtod2
ID: 35510061
Yes, I even tried to pronunciate your name.  Did you see my video response?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35510176
Yes I had to get a pair of earphones to be able to listen to you.

You can try to pronounce it like sarkib. I would pronounce the q from the throat rather than the back of the tongue. Frequently people in my country, Pakistan, tend to deliver it from the tongue instead of the throat.

About the filename extension, I have been doing this on 2003 so used the xls extension. Try changing this to xlsx instead. I hope this will take care of the problem. I shall come back on the desktop issue.

Saqib

0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 35510224
See if this works
Sub ws2newfile()
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

0
 

Author Comment

by:rtod2
ID: 35510248
Thanks for continuing with this.

I used Excel 2010 and Windows 7 to run the test.

It did work without error and stripped the macros from the file.  It did not take auto-select the current user's desktop as the location.  

Getting closer clearly.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35510315
>>> It did not take auto-select the current user's desktop as the location.  

Did it take a different user's desktop or was it not the desktop?

Can you step through the lines and see what value does the variable dtpath take?
0
 

Author Closing Comment

by:rtod2
ID: 35513668
Pretty darn sure that does it, Thank You!
0
 

Author Comment

by:rtod2
ID: 35513822
Here is a follow-up question from this.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

850 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