Select Named Range in Worksheet

[Dee]

Hi,

I'm trying to add code to an option button on a worksheet that hides rows on
its worksheet, plus hides columns on another worksheet, plus hides rows on
another worksheet.

I have named my ranges, which I refer to in the code. I tried many
variations that I have found in the last few hours in this group and other
places in the Internet, but as soon as I try to hide the columns/rows on a
worksheet other than the active one that contains the option button, I end up
with errors.

Here is my code: Any help is really appreciated!

Private Sub OptionButton1_Click()

Application.ScreenUpdating = False

With ThisWorkbook.Worksheets("Data")
Range("FebtoDec").Select
Selection.EntireRow.Hidden = True
End With

With Worksheets("Distribution").Range("DistributionFebtoDec")
.EntireColumn.Hidden = True
End With

With Worksheets("Summary").Range("SummaryFebtoDec")
.EntireRow.Hidden = True
End With

Application.ScreenUpdating = True

End Sub

 

[Tom Hutchins]

You can't select cells that aren't on the active sheet. Your code to select a
range on the Data sheet fails if it's not the active sheet when you run the
nacro. Try this slight variation:

Private Sub OptionButton1_Click()
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Data").Range("FebtoDec")
.EntireRow.Hidden = True
End With
With Worksheets("Distribution").Range("DistributionFebtoDec")
.EntireColumn.Hidden = True
End With
With Worksheets("Summary").Range("SummaryFebtoDec")
.EntireRow.Hidden = True
End With
Application.ScreenUpdating = True
End Sub

 

[Dee]

Thanks, Tom. This worked perfectly. If I have another range in the same
workbook, would this code be correct? Thanks again.

Private Sub OptionButton1_Click()
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Data").Range("FebtoDec")

.EntireRow.Hidden = True
Range("AnotherNamedRange").EntireRow.Hidden = True

End With

 

[Tom Hutchins]

You can't embed the statement for "AnotherNamedRange" inside the With...End
With construction for a different range. Just add a With...End With
construction for the additional range, similar to the others:

With ThisWorkbook.Worksheets("Data").Range("AnotherNamedRange")
.EntireRow.Hidden = True
End With

Hutch