Code to Unhide All Columns Upon Deactivate Tab (when I change from tab 1 to tab 2, unhide tab 1 columns)

johnlucasprice

New Member
Joined
Sep 18, 2009
Messages
15
Summary: I need to perform an action (unhide all columns) whenever I switch to a different worksheet.
Detail: I have a file with 2 tabs (worksheets). Tab 1 contains daily data, and when people are done with the current week they group and hide the columns for that week. This allows them to only view the new week and do an easy copy/paste into other applications. Tab 2 totals up the daily data from Tab 1 and shows monthly totals. The problem is that when they hide Tab 1 columns for past days, the formulas in Tab 2 don't "find" that hidden data. I would like to write a basic code that unhides all Tab 1 columns when I switch to Tab 2 so the formulas on Tab 2 reflect accurate totals. My thought is that it would be Worksheet code on Tab 1 using "Deactivate". I've tried to piece together different bits of code but can't get anything to work properly. I don't want them to have to run a macro or click a button, I'd like it to be automated when they switch tabs. Any help is greatly appreciated.
Lucas
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

BIGTONE559

Active Member
Joined
Apr 20, 2011
Messages
336
this is something simple. . . right now it only does the active sheet however you can make changes to perform the code on the proper page.
Code:
Sub UnHideMe()

For Each col In ActiveSheet.Columns
col.EntireColumn.Hidden = False
Next col
End Sub
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,047
Office Version
2013
Platform
Windows
Lucas,

Perhaps try....
Code:
Private Sub Worksheet_Deactivate()
Range(Cells(1, 1), Cells(1, Columns.Count)).EntireColumn.Hidden = False
End Sub
Hope that helps.
 

johnlucasprice

New Member
Joined
Sep 18, 2009
Messages
15
Thanks guys! Actually Tony's code worked perfectly with the "Deactivate" function I wanted to use. Very simple and effective.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,046
Messages
5,466,236
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top