Hiding a sheet based upon a cell value

Resolved · High Priority · Version 2010

Tue 5 Jan 2016, 15:26 Delegate Alex said...

Alex has attended:
Excel Advanced course

Hiding a sheet based upon a cell value

Hi,

I am looking to unhide a worksheet that has the same name as a value of a cell in a table. It is part of a larger overall code.

ActiveCell.Offset(0, 14).Select

'I need to get this value to be defined as "sheet"

Sheets("Data").Select
Sheets(sheet).Visible = True

Thanks

Tue 5 Jan 2016, 16:32 Trainer Doug said...

RE: Hiding a sheet based upon a cell value

Hi Alex

Your code looks fine

'First define a text variable called sheet

Dim sheet As String

ActiveCell.Offset(0, 14).Select

'Store the value into sheet
sheet = ActiveCell.Value

Sheets("Data").Select
'make visible the worksheet with name stored in sheet
Sheets(sheet).Visible = True

You don't actually have to select the cell containing the sheet name. You can write...

sheet = ActiveCell.Offset(0, 14).Value

as long as the active cell in 14 cells to the left.

Hope that helps for now Alex.

Regards
Doug
Best STL

Thu 7 Jan 2016, 16:25 Trainer Marius said...

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

Thu 14 Jan 2016: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Pivot table grouping

If you want to group items in a pivot table together, simply highlight the labels for the fields either with the shift key (if adjacent)or with the contral key if they are not next to each other.
Right click and choose group. Give the cell a name.

When you double click on this cell it will either expamd or collapse your grouped area

View all Excel hints and tips