Help on a button to export a specified sheet to a new xl file

[Jhankins]

I wish to create a button to export a selected sheet to a new workbook, all i wish them to enter a file name.
Can this be done??

 

[Bowers74]

Hi Jhankins,

goto this FAQ: faq707-2482

This should get you on the right track.

If you are moving the worksheet to a "new" workbook, then why should the user have to give a file name. Or did you mean "export a selected sheet to ANOTHER workbook"?

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[bryanbayfield]

Use the control toolbox toolbar to draw a command button on your workbook - I have left t as command button 1, rename it to whatever you like, but if you do you'll have to edit the code below.

In design mode double click on the button and insert the code below into the shell code.

Take design mode off and the button should work.

[tt]Dim varSaveName As Variant, wbkActive As Workbook, shpSearch As Shape
Set wbkActive = ActiveWorkbook
varSaveName = Application.GetSaveAsFilename(Filefilter:="Excel Workbooks (*.xls),*.xls&quot
If varSaveName = False Then Exit Sub
ActiveSheet.Copy
For Each shpSearch In ActiveSheet.Shapes
If shpSearch.Name = "CommandButton1" Then shpSearch.Delete
Next
ActiveWorkbook.SaveAs (varSaveName)
wbkActive.Activate[/tt]

 

[Jhankins]

Cheers,

Thanks for the help worked a treat.


Kodus to you all.