Franz Verga wrote:
Wuddus wrote:
I have a very simple macro that, when run, takes the user to a
specified location on another sheet in the same workbook:

Sub ToProjectInfoPage()
Sheets("Project Info").Select
Range("A1:E1").Select
End Sub

This works fine when the destination sheet ("Project Info") is
visible, but when it's hidden, the macro returns an error.

Is there a way to fix the macro so that it works when the destination
sheet is hidden?

Thanks in advance!


Hi Wuddus,

the error raise because you cannot select an hidden sheet and also you
cannot select a range on an hidden sheet...

So you can modify your macro in this way:

Sub ToProjectInfoPage()
With Sheets("Project Info")
.Visible = xlSheetVisible
.Range("A1:E1").Select
End with
End Sub

or if you don't want to make your sheet visible you can use this one:

Sub ToProjectInfoPage()

Application.ScreenUpdating = False

With Sheets("Project Info")
.Visible = xlSheetVisible
.Range("A1:E1").Select
'other code for calculations
End with

Application.ScreenUpdating = True

End Sub

I forgot a line in both macro... You cannot select a range on a sheet
without selecting the sheet... So the macro should be:

Sub ToProjectInfoPage()
With Sheets("Project Info")
.Visible = xlSheetVisible
.Select
.Range("A1:E1").Select
End with
End Sub

if you want to see the previously hidden sheet, and:

Sub ToProjectInfoPage()

Application.ScreenUpdating = False

With Sheets("Project Info")
.Visible = xlSheetVisible
.Range("A1:E1").Select

'other code for calculations

.Visible = xlSheetHidden

End with

Application.ScreenUpdating = True

End Sub


if you don't want show the hidden sheet.

Anyway, as also suggested from Dave, you can work on a range based on an
hidden sheet without selecting it...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy