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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

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,162
Office Version
  1. 2013
Platform
  1. 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,129,791
Messages
5,638,340
Members
417,021
Latest member
moon miner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top