Making Sheet Visible
 

Do you know the hidden worksheet's name for certain? You typed "Sheet 2" and
typically sheet names given by Excel don't have a space in them. If you have
the sheet name correct, I suspect the workbook and worksheet are protected
with a password.

First thing to do is to create a new workbook and name the sheets in it the
same as the sheets in the workbook with the formulas you need. This will
help prevent #REF errors from showing up when the formulas are copied into
the new book.

I've always wanted to try this code - now you can be my guinea pig. It
actually worked for me - both books were open, the code goes into the new
workbook. I'm not so certain you couldn't use a copy | paste special
operation to do it, but this works.

Sub ReadAnotherSheet()
Const sourceBook = "FileWHiddenSheet.xls" ' change
Const sourceSheet = "TheHiddenSheet" ' change
Dim anyCell As Object

'in this workbook
' use same name in both books
Worksheets(sourceSheet).Select
For Each anyCell In Workbooks(sourceBook). _
Worksheets(sourceSheet).UsedRange
If anyCell.HasFormula Then
ActiveSheet.Range(anyCell.Address).Formula _
= anyCell.Formula
Else
ActiveSheet.Range(anyCell.Address).Value _
= anyCell.Formula
End If
Next
End Sub



"Emma Hope" wrote:

Hi All,

The predecessor in my job created a very useful spreadsheet with a hidden
sheet that contains all the formulae we use, we now need to change these
formulae to account for tax changes in the UK Budget, we don't have the
password (& we can't contact the person as he is now travelling abroad) to
unprotect the workbook and so we can't change them.

I wish to either copy the worksheet or make it visible in someway so i can
look at the formulae, i tried Sheets("Sheet 2").visible = true and that
didn't work.

I don't want to buy a password cracking tool and linking to the hidden sheet
only shows the values, not the formulae.

Can anyone help.
thanks