Is it possible to save just the active sheet in excel? 2

[coolcarls]

I have a workbook with a bunch of stuff in and the file size is quite large, I want to save JUST the sheet I am woking on as it's own workbook.
I hope it is possible
Carl

 

[bryanbayfield]

Right click on the sheet tab > Move/ Copy > Move to a new workbook.

Select new workbook, delete all sheets except the one you copied over > Save as Workbook.

 

[coolcarls]

Thanks for the answer Bryan, I am afraid that I was unclear in my question. I was looking at automating the process with a module or something, any ideas on that?

 

[bryanbayfield]

Put this in the Personal.xls code module then assign a command button for it on the toolbar:

Sub Copy_Sheet()
Dim sht_name As String, filesavename As Variant, clean_str As String, i As Integer
On Error GoTo bad_name
sht_name = Application.InputBox(Prompt:="Enter Name for Copied Sheet", Type:=2)
ActiveSheet.Copy
ActiveSheet.Name = sht_name
filesavename = Application.GetSaveAsFilename(initialfilename:=ActiveSheet.Name, _
                                                filefilter:="Excel Workbook (*.xls), *.xls")
If filesavename <> &quot;&quot; And filesavename <> False Then: ActiveWorkbook.SaveAs Filename:=filesavename
Exit Sub
bad_name:
    If Len(sht_name) = 0 Then: sht_name = Application.UserName & &quot;~&quot; & Day(Date) & &quot;.&quot; & _
                                         Month(Date) & &quot;.&quot; & Year(Date)
    If Len(sht_name) > 31 Then: sht_name = Left(sht_name, 31)
    For i = 1 To Len(sht_name)
        Select Case Asc(Mid(sht_name, i, 1))
            Case 42, 47, 58, 63, 91 To 93
                'do nothing
            Case Else
                clean_str = clean_str & Mid(sht_name, i, 1)
            End Select
        Next
        sht_name = clean_str
        Resume
End Sub

I've tried it out and it should be foolproof, but let me know if you have any problems.

Bryan. :

 

[bryanbayfield]

By the way, if you want to remove the active sheet from the workbook, as well as copying it to another workbook, then use

Activesheet.Move

instead of

ActiveSheet.Copy

 

[coolcarls]

Well that's exact! Thanks a lot
Carl

 

[DaleWatson123321]

Bryan,

GOOD contribution to have on hand. ====> STAR. Thanks.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[coolcarls]

Bryan
Is there any way to have the new workbook not open when saved?
What's happening is that I hit the save button (which works incredibly well) and the sheet is saved and openned, is it possible to have it not open and just stay with the workbook I am working on?
I will post this as a new question as well.
Thanks

 

[bryanbayfield]

Carl:

Use:

ActiveWorkbook.Close(SaveChanges:=True, FileName:=filesavename)

Instead of:

ActiveWorkbook.SaveAs Filename:=filesavename

This should get you back to the original workbook. But since I haven't tried it myself, let me know if you have any probs.

Bryan.

 

[coolcarls]

hee hee....you're good
Cheers!