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]

Error 9 comes when application doesn't find the string i.e. worksheet name.

So is Sheet23 really named as Sheet23.

This will work if you have more than 23 sheets:

wbMS.Worksheets(23).Visible = xlVeryHidden

 

[The-Duke]

Error 9 comes when application doesn't find the string i.e. worksheet name.

So is Sheet23 really named as Sheet23.

This will work if you have more than 23 sheets:

wbMS.Worksheets(23).Visible = xlVeryHidden

It's the only worksheet, it is just in there named as Sheet23. So do I reference the number of worksheet that it is (1) or back to it's name. I'm pretty sure it will be the (1) and i'll try that now.

 

[taurean]

Congratulations, you have just found a possibility which I had not considered .

If this is the ~ONLY~ sheet then Excel won't let you do that! Insert a blank sheet and then try to hide this sheet.

Like:

wbMS.Sheets.Add Before:=Sheets(1)
wbMS.Worksheets("Sheet23").Visible = xlSheetVeryHidden

 

[The-Duke]

That worked and hid the sheet just like I wanted it to, many thanks.

Now I guess I do have a problem with deleting the similar worksheet that is being replaced before I do my copy. It's name is Sheet23, but there is no guarantee that it will be in a certain sequential order so if the number refers to the order the sheet is in that wouldn't quite work.

 

[buxtongt]

You can refer to sheets by name instead of number. For example:

Sheets("old").Visible = True

 

[taurean]

That worked and hid the sheet just like I wanted it to, many thanks.

Now I guess I do have a problem with deleting the similar worksheet that is being replaced before I do my copy. It's name is Sheet23, but there is no guarantee that it will be in a certain sequential order so if the number refers to the order the sheet is in that wouldn't quite work.

So we are halfway. I take that your original line of coding shall work without hassles which is:

Application.DisplayAlerts = False
wbMS.Worksheets("Sheet23").Visible = True
wbMS.Worksheets("Sheet23").Delete
Application.DisplayAlerts = True

 

[The-Duke]

This is the code that worked for me hiding the sheet:

wbMS.Worksheets(1).Visible = xlSheetVeryHidden

The number there works fine because in the template that sheet will always be the first sheet as we go through this process.

and i'm about to try this code to delete the similar sheet in the existing xls that the sheet in the template is replacing:

wbFF.Sheets("DCR Tables").Delete

We'll see if this works...

EDIT::

No,

error 1004: Delete methodof worksheet class failed

I didn't think that syntax was right

 

[taurean]

The method is correct:
http://msdn.microsoft.com/en-us/library/aa223863(v=office.11).aspx

 

[The-Duke]

With this code:

Application.DisplayAlerts = False
wbFF.Worksheets("Sheet23").Visible = True
wbFF.Worksheets("Sheet23").Delete
Application.DisplayAlerts = True

I got Error 9 again, it's in the other workbook, that's why it's wbFF.

Under properties for that sheet in VBA there is:

(Name): Sheet23
DisplayPageBreaks: False
DisplayRighttoLeft: False
EnableAutoFilter: False
EnableCalculation: True
EnableOutlining: False
EnablePivotSelection: False
EnableSelection: 0 - xlNoRestrictions
Name: DCR Tables
Scorll Area:
StandardWidth: 8.43
Visible: 0 - xlSheetHidden