Save Workbook Question

[AlexanderBB]

Hi,

I'm after a way to save my workbook without saving one sheet.
The sheet may or may not exist (depending on user choice) but if loaded
I don't want it to become part of the main workbook.
I thought I could just unload it before saving, and reload it after the save but the time taken is excessive.

And if it's not possible, please let me know and I'll try to figure out something else.

Thanks, ABB

[westconn1]

how are you doing now?

[AlexanderBB]

Doing the unload, do you mean ??

Set wb1 = ActiveWorkbook
    If SheetLoaded = True Then
     Set wb2 = Workbooks.Open(ThisWorkbook.Path & "\Book2.xls")
     Application.DisplayAlerts = False
     wb2.Sheets("Sheet2").Delete
     wb1.Sheets("Sheet2").Copy After:=wb2.Sheets(wb2.Sheets.Count)
     wb2.Close SaveChanges:=True
    End If
    
    On Error Resume Next
    wb1.Sheets("Sheet2").Delete
    On Error GoTo 0
Set wb2 = Nothing
Set wb1 = Nothing

Function SheetLoaded() As Boolean
On Error GoTo err_NoSheet
With Worksheets("Sheet2")
End With
SheetLoaded = True
Exit Function
err_NoSheet:
End Function

[westconn1]

I thought I could just unload it before saving, and reload it after the save but the time taken is excessive.

the code does not appear to do quite what is requested in the original post

you can try like this, as it does not open any workbooks, should be quicker

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myarr As Variant, s As Worksheet
 
    For Each s In ThisWorkbook.Sheets
        If s.Name = "Sheet2" Then
            Application.DisplayAlerts = False
            myarr = s.UsedRange
            s.Delete
            Exit For
        End If
    Next
    Cancel = True
    ThisWorkbook.Save
    If Not VarType(myarr) = 0 Then
        Set s = ThisWorkbook.Sheets.Add
        s.Name = "Sheet2"
        s.Range(s.Cells(1, 1), s.Cells(UBound(myarr, 1), UBound(myarr, 2))) = myarr
    End If
End Sub

test thoroughly

[AlexanderBB]

Thanks Westconn. Played around with this and some great stuff Koolsid gave me a few weeks ago, but I think the Workbooks are too big and the time taken overall spoils things. I can see your routine would work quite nicely - apart from losing the sheet formatting. I think now my whole idea with loading a conditional sheet as required just isn't viable

Still, interesting to try and thnks for the help

Regards, ABB

[westconn1]

if you do not want to save it, what does it matter about the formatting?

you could do a save as without the sheet2, then some file renaming

[AlexanderBB]

>if you do not want to save it, what does it matter about the formatting?

Ideally it should remain just as before Saving i.e. continue as usable and with formatting.
Yes it was possible to save the rest of the workbook excluding this sheet, but the two outcomes (a very long time with unload/reload, or lose formatting) made neither practical. Both workbooks are 25-30 megs.

>you could do a save as without the sheet2, then some file renaming

It's for a number of other users so would need to be simple and bullet proof !

But thank you (and koolsid) for letting me explore the idea. It was worth trying and I learnt some new things

Regards, ABB