Hide Worksheet in a Seperate Workbook

T

The-Duke

New Member
Joined
Oct 12, 2011
Messages
21
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:
Code: 
wbMS.Worksheets("Sheet23").Visible = xlSheetVeryHidden

and this code:
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
 
Sort by date Sort by votes
Change this line:
Code: 
wbFF.Worksheets("Sheet23").Delete
to
Code: 
wbFF.Worksheets("DCR Tables").Delete
And then let us know the result. But I am suspecting that you are trying to move this sheet from 'wbFF' to 'wbMS'. Is it so?
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
taurean said:
Change this line:
Code: 
wbFF.Worksheets("Sheet23").Delete
to
Code: 
wbFF.Worksheets("DCR Tables").Delete
And then let us know the result. But I am suspecting that you are trying to move this sheet from 'wbFF' to 'wbMS'. Is it so?
Click to expand...

I got error 1004 again with that code.

I'm replacing the worksheet in FF with the one in the MS as well as some security code in VBA. I'm automating this as much as possible for end users to update xls on their own machine.

I could get around this by just not copying that sheet, but that I am also a little unsure about. I tried it that way but got stuck. here is the code I am using to copy the worksheets from FF to MS

Code: 
For Each Sheet In wbFF.Sheets
        Sheet.Copy After:=wbMS.Sheets(wbMS.Sheets.Count)
Next Sheet

I would just need to add some sort of IF statement or other qualifier but then I get back into the quandary of how to address the sheet and by what name/location
 
Upvote 0
OK. I could not re-create your problem with this code:
Code: 
wbFF.Sheets("DCR Tables").Delete

Can you post your complete code please?
 
Upvote 0
taurean said:
OK. I could not re-create your problem with this code:
Code: 
wbFF.Sheets("DCR Tables").Delete

Can you post your complete code please?
Click to expand...

As it is now:

I get the file name from this first process and store it in a text box that I have on a form in excel

Code: 
Private Sub cmdGetFile_Click()
On Error GoTo HandleError
    Dim fn As String
 
    fn = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")
    Me.txtFileName = fn
 
    Exit Sub
 
HandleError:
    MsgBox "Error: " & Err.Number & vbNewLine & "Description: " & Err.Description, vbCritical, "Error"
    Exit Sub
End Sub

And this is the process that copies over the tables and I need to delete the DCR Tables tab in the FF

Code: 
Private Sub cmdRectifyPath_Click()
On Error GoTo HandleError
 
    Dim wbMS As Workbook
    Dim wbFF As Workbook
    Dim strMSwb As String
    Dim strFFwb As String
 
    'Verify that a FF has been chosen
    If Me.txtFileName.Value = "" Then
        MsgBox "No file selected or could not find file", vbOKOnly, "Error Finding File"
        Me.txtFileName = ""
        Exit Sub
    Else
        strFFwb = Me.txtFileName
    End If
 
    'MS location
    strMSwb = "C:\FileLocation\MS.xls"
 
    'Open the FF and the MS
    Set wbFF = Workbooks.Open(strFFwb)
    Set wbMS = Workbooks.Open(strMSwb)
 
    [B]'Delete old DCR Sheet in FF[/B]
[B]   Application.DisplayAlerts = False[/B]
[B]   Workbooks(strFFwb).Worksheets("DCR Tables").Delete[/B]
[B]   Application.DisplayAlerts = True[/B]
    
 
    'Copy worksheets from old FinForm to the new Master Shell
    For Each Sheet In wbFF.Sheets
        Sheet.Copy After:=wbMS.Sheets(wbMS.Sheets.Count)
    Next Sheet
 
    'Hide DCR sheet in MS
    wbMS.Worksheets(1).Visible = xlSheetVeryHidden
 
    'Add _OLD suffix to original FF
    Application.DisplayAlerts = False
    wbFF.SaveAs Filename:=strFFwb & "_OLD", FileFormat:=xlNormal, Password:=""
 
    'Rename Open MS to old FF name
    wbMS.SaveAs Filename:=strFFwb, FileFormat:=xlNormal, Password:=""
 
    wbFF.Close
    wbMS.Close
 
    Application.DisplayAlerts = True
 
    Me.txtFileName = ""
 
    Exit Sub
 
HandleError:
    MsgBox "Error: " & Err.Number & vbNewLine & "Description: " & Err.Description, vbCritical, "Error"
    Exit Sub
End Sub

Everything is workind except for the deletion of that tab. At the moment I am getting error code 9, subscript out of range
 
Upvote 0
Try the following code:
Code: 
Private Sub cmdRectifyPath_Click()
On Error GoTo HandleError
 
    Dim wbMS As Workbook
    Dim wbFF As Workbook
    Dim strMSwb As String
    Dim strFFwb As String
 
    'Verify that a FF has been chosen
    If Me.txtFileName.Value = "" Then
        MsgBox "No file selected or could not find file", vbOKOnly, "Error Finding File"
        Me.txtFileName = ""
        Exit Sub
    Else
        strFFwb = Me.txtFileName
    End If
 
    'MS location
    strMSwb = "C:\FileLocation\MS.xls"
 
    'Open the FF and the MS
    Set wbFF = Workbooks.Open(strFFwb)
    Set wbMS = Workbooks.Open(strMSwb)
 
    [B]'Delete old DCR Sheet in FF[/B]
[B]   Application.DisplayAlerts = False[/B]
[B]   wbFF.Sheets("DCR Tables").Delete[/B]
[B]   Application.DisplayAlerts = True[/B]
    
 
    'Copy worksheets from old FinForm to the new Master Shell
    For Each Sheet In wbFF.Sheets
        Sheet.Copy After:=wbMS.Sheets(wbMS.Sheets.Count)
    Next Sheet
 
    'Hide DCR sheet in MS
    wbMS.Worksheets(1).Visible = xlSheetVeryHidden
 
    'Add _OLD suffix to original FF
    Application.DisplayAlerts = False
    wbFF.SaveAs Filename:=strFFwb & "_OLD", FileFormat:=xlNormal, Password:=""
 
    'Rename Open MS to old FF name
    wbMS.SaveAs Filename:=strFFwb, FileFormat:=xlNormal, Password:=""
 
    wbFF.Close
    wbMS.Close
 
    Application.DisplayAlerts = True
 
    Me.txtFileName = ""
 
    Exit Sub
 
HandleError:
    MsgBox "Error: " & Err.Number & vbNewLine & "Description: " & Err.Description, vbCritical, "Error"
    Exit Sub
End Sub
 
Upvote 0
taurean said:
Try the following code:
Code: 
Private Sub cmdRectifyPath_Click()
On Error GoTo HandleError
 
    Dim wbMS As Workbook
    Dim wbFF As Workbook
    Dim strMSwb As String
    Dim strFFwb As String
 
    'Verify that a FF has been chosen
    If Me.txtFileName.Value = "" Then
        MsgBox "No file selected or could not find file", vbOKOnly, "Error Finding File"
        Me.txtFileName = ""
        Exit Sub
    Else
        strFFwb = Me.txtFileName
    End If
 
    'MS location
    strMSwb = "C:\FileLocation\MS.xls"
 
    'Open the FF and the MS
    Set wbFF = Workbooks.Open(strFFwb)
    Set wbMS = Workbooks.Open(strMSwb)
 
    [B]'Delete old DCR Sheet in FF[/B]
[B]  Application.DisplayAlerts = False[/B]
[B]  wbFF.Sheets("DCR Tables").Delete[/B]
[B]  Application.DisplayAlerts = True[/B]
 
 
    'Copy worksheets from old FinForm to the new Master Shell
    For Each Sheet In wbFF.Sheets
        Sheet.Copy After:=wbMS.Sheets(wbMS.Sheets.Count)
    Next Sheet
 
    'Hide DCR sheet in MS
    wbMS.Worksheets(1).Visible = xlSheetVeryHidden
 
    'Add _OLD suffix to original FF
    Application.DisplayAlerts = False
    wbFF.SaveAs Filename:=strFFwb & "_OLD", FileFormat:=xlNormal, Password:=""
 
    'Rename Open MS to old FF name
    wbMS.SaveAs Filename:=strFFwb, FileFormat:=xlNormal, Password:=""
 
    wbFF.Close
    wbMS.Close
 
    Application.DisplayAlerts = True
 
    Me.txtFileName = ""
 
    Exit Sub
 
HandleError:
    MsgBox "Error: " & Err.Number & vbNewLine & "Description: " & Err.Description, vbCritical, "Error"
    Exit Sub
End Sub
Click to expand...

Error 1004
Delete Method of Worksheet class failed

The worksheet I am trying to delete is in there as xlSheetVeryHidden. Is this an issue and I need to unhide it first or should it delete it anyway?
 
Upvote 0
Yep, I guess you have nailed it this time around! Add this line before deletion as below.
Code: 
[B]'Delete old DCR Sheet in FF[/B]
[B]  Application.DisplayAlerts = False[/B]
[B]  wbFF.Sheets("DCR Tables").Visible = xlSheetVisible[/B]
[B]  wbFF.Sheets("DCR Tables").Delete[/B]
[B]  Application.DisplayAlerts = True[/B]
 
Upvote 0
taurean said:
Yep, I guess you have nailed it this time around! Add this line before deletion as below.
Code: 
[B]'Delete old DCR Sheet in FF[/B]
[B] Application.DisplayAlerts = False[/B]
[B] wbFF.Sheets("DCR Tables").Visible = xlSheetVisible[/B]
[B] wbFF.Sheets("DCR Tables").Delete[/B]
[B] Application.DisplayAlerts = True[/B]
Click to expand...

Error 1004
Unable to set the Visible property of the Worksheet class

It seems as if it is getting into the file, it just refuses to finish the process for reasons unknown.
 
Upvote 0
I am running out of imagination:
Is the workbook protected?
 
Upvote 0
taurean said:
I am running out of imagination:
Is the workbook protected?
Click to expand...

No, I can just double click on it to open it. It does make me click enable macros when I do that, but so does the MS xls and I was able to hide the DCR Tables tab in that one. I am able to copy from FF to MS as well. I might be able to try changing my copy routine to not copy the DCR tables from FF, but I may run into the same problems of recognizing the correct worksheet.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Worksheet Change - multiple Loops in one sub
2
Replies
15
Views
791
mumps
mumps
P
  • Question Question
Showing Worksheets Problem
2
Replies
18
Views
466
Alex Blakenburg
A
T
  • Question Question
Automatically Copying Data To A New Workbook When Closing A Workbook
Replies
4
Views
393
Tmini
T
B
  • Question Question
VBA exports worksheet from XLSM to XLXS matching all source properties doesn't keep print area
Replies
0
Views
80
brentcole
B
L
  • Locked
  • Question Question
How set range for chart in powerpoint created from vba
Replies
0
Views
70
Legacy 519704
L

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top