Hide Worksheet in a Seperate Workbook

[The-Duke]

I have an excel file that opens two seperate excel files, copies all the tabs from a pre-existing xls to a template xls, saves them both with new names and then closes both. Everything is working fine, except I need to delete a pre-existing worksheet in the original file and then hide the worksheet I want to keep that is replacing the deleted worksheet in the template file before I save.

I've tried this code to try the hide the worksheet:

wbMS.Worksheets("Sheet23").Visible = xlSheetVeryHidden

and this code:

wbMS.Sheet23.Visible = xlSheetVeryHidden

but I get Error 9: Subscript out of range when I try this. I'm just gettting back into Excel VBA programming after a pretty long layoff and this seems like something simple but I can't plave my error.

Deleting the worksheet I don't want should be able to follow the same kind of logic for whatever works for hiding a worksheet, just using .delete

 

[taurean]

What version of Excel are you using?

 

[The-Duke]

what version of excel are you using?

2003

 

[ElBombay]

Duke,

Welcome back to the joy & horrors of VBA coding.

I'm looking to refine the code but I've had this utility in production (after mikerickson's help) for a few years so maybe the code will help you. You can add cases to trap '9' or '13' to branch specifically and leave surprizes to the Case Else.

HTH

'----------------------------------------------------------------------------
Function zCopy_Sheet(cSource, cNewName, cNextSheet) As Boolean
'
' Copy_Sheet Macro
' Macro recorded 11/10/2008 by Jim at Network for HWM
' Improved (handled 'Cancel' button) by Post from mikerickson 11/23/08
' Option "New" added 9/7/11, After:=Sheets(Sheets.Count) added during test on 10/4/11
'
Dim iSheetsCount As Integer

    On Error GoTo CS_Err
    If cSource = "New" Then
       If cNextSheet = "" Then
          ActiveSheet.Names.Add
          
        Else
          ActiveSheet.Names.Add before:=Sheets(cNextSheet)
          
        End If
        
    ElseIf cNextSheet = "" Then
        Sheets(cSource).Copy After:=Sheets(Sheets.Count)    'W/o "After", New Wbook is created
                                                            '       in event of "same-name" conflict
    Else
        Sheets(cSource).Copy before:=Sheets(cNextSheet)
        
    End If
    ActiveSheet.Name = cNewName

CS_Err:
    Select Case Err.Number
    Case 0                  'Skip Error-handler section
        zCopy_Sheet = True
        
    Case 1004                       '"Same name" error
        iSheetsCount = Sheets.Count
        Sheets(cNewName).Delete     'This will produce "DEL/Cancel" box
        If Sheets.Count <> iSheetsCount Then        'A sheet was deleted
               Resume                                  'Run command that caused the Error

        Else                                        'User must have chosen "Cancel"
            Application.DisplayAlerts = False
                ActiveSheet.Delete               'Delete the new sheet; abort the update
            Application.DisplayAlerts = True
            zCopy_Sheet = False
            MsgBox "Update Canceled"
       
        End If
    
    Case Else                       'Abort on unexpected error
        zMisc_Err ""                   'Generic "ELSE option"
        
    End Select
    On Error GoTo 0
    
End Function    'Copy_Sheet
'----------------------------------------------------------------------------

 

[taurean]

Just check the following in wbFF
Tools >> Protection (Not sure about tab name) and see if the workbook has been protected for Structures.

 

[The-Duke]

Just check the following in wbFF
Tools >> Protection (Not sure about tab name) and see if the workbook has been protected for Structures.

I think you are onto something, it has been protected for Structure

 

[The-Duke]

It's working now that I added wbFF.Unprotect and wbFF.Protect before and after the delete statement. My last remaining problem is that while it is protecting the worksheets still when I reopen it, but the workbook isn't protected until I go to Tools > Protection > Protect Workbook

'Delete old DCR Sheet in FF
Application.DisplayAlerts = False
wbFF.Unprotect Password:="PASSWORD"
wbFF.Worksheets("DCR Tables").Visible = xlSheetVisible
wbFF.Worksheets("DCR Tables").Delete
wbFF.Protect Password:="PASSWORD", Structure:=True, Windows:=False
Application.DisplayAlerts = True

 

[The-Duke]

I got it now, I needed to apply protection to the MS xls, that was my issue.

Thank you very much for your help Taurean, you've been a life saver.

 

[taurean]

Glad that it's solved. At #17, I had asked for it but then you replied it "NO" as probably you took it for the Workbook protection instead of file protection. So just took a blind stab.

But then my knowledge is borrowed. You'll have to thank these guys
http://www.ozgrid.com/forum/showthread.php?t=19914&page=1