Generating all macros to work across worksheets when opening

Fmedia

New Member
Joined
Oct 10, 2006
Messages
7
Hi All

I have a major problem. I have several excel worksheets which pulls in data from a master. Some of the information doesn't fall within the criteria so I hide it with a macro. When I open the work book the sheet that I am on automatically triggers the macro. The problem i have is that the other sheets do not automatically update the same way, so If I was looking at another sheet I would manually have to implement the macro. Any ideas on how I can get all the macros to run on all worksheets when opening the workbook? Any help would be appreciated. I have detailed the exact macro I use if helpful below (its basically: unhide everything, then hide row a if=false)

Private Sub Workbook_Open()
Dim c As Range, rng
Set rng = Range("a:a")

Cells.Select
Selection.EntireColumn.Hidden = False
Selection.EntireRow.Hidden = False

For Each c In rng
If c.Value = "False" Then
c.EntireRow.Hidden = True
End If
Next c

End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows
Hi

You could put your code in the "This workbook" module, within the Workbook_open event (so that it automatically runs when you open the workbook) and include a for....each statement to iterate through all of the worksheets you want to be included (i.e. NOT you master sheet I assume,) and put your code within the for.....each.

Something like:

Code:
For each worksheet in this workbook
if worksheet.name <> "Your_Master's_name" then
your code in here
next worksheet
 

Fmedia

New Member
Joined
Oct 10, 2006
Messages
7
Thanks for your help Sykes

Unfortunatly this isn't quite working out. I am a complete novice where macro's are concerned, and have extremely basic skills so I suppose i am implementigng your advice incorrectly. This is how I have it..any advice?


Private Sub Workbook_Open()
Dim c As Range, rng
Set rng = Range("a:a")

For each worksheet in this workbook
If Worksheet.Name <> "target achievement.xls" Then

Cells.Select
Selection.EntireColumn.Hidden = False
Selection.EntireRow.Hidden = False

For Each c In rng
If c.Value = "False" Then
c.EntireRow.Hidden = True
End If
Next c

Next Worksheet

End Sub
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows
Ah, Ok!

Always assuming your code works "as is", then to out it into my idea, it would look thus:
Code:
Private Sub Workbook_Open()
Dim ws As Worksheet
Dim c As Range, rng

Set rng = Range("a:a")

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "target achievement.xls" Then
    
        Cells.Select
        Selection.EntireColumn.Hidden = False
        Selection.EntireRow.Hidden = False
        
        For Each c In rng
            If c.Value = "False" Then
            c.EntireRow.Hidden = True
            End If
        Next c
    
    End If
Next

End Sub
The name of the worksheet to test is case sensitive, so be sure that "target achievement.xls" is spealt exactly as per the worksheet.
........and don't forget that to test it, you'll have to save the workbook, then close and re-open.
 

Fmedia

New Member
Joined
Oct 10, 2006
Messages
7

ADVERTISEMENT

Thanks Sykes

The macro is now working however is just calling the active worksheet. Just to clarify, it is the work book that is called 'target achievement.xls, not the worksheet. Do I have to call every individual worksheet?

Thanks
Jonathan
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows
Jonathan

I have a major problem. I have several excel worksheets which pulls in data from a master.
OK, I assumed that you didn't want the code to run on your "master" sheet, and intended for you to put the "master" sheet's name into my code. (I wondered why your sheet was called blah ".xls" !!

Do you want to run the code in every sheet in the book? If so this should do the trick:

Code:
Private Sub Workbook_Open()
Dim ws As Worksheet
Dim c As Range, rng

Set rng = Range("a:a")

For Each ws In ThisWorkbook.Worksheets
    
        Cells.Select
        Selection.EntireColumn.Hidden = False
        Selection.EntireRow.Hidden = False
       
        For Each c In rng
            If c.Value = "False" Then
            c.EntireRow.Hidden = True
            End If
        Next c
   
Next

End Sub

If as I suspected, you don't want your "master" affected, just keep the original code I gave you, and put the name of the "master" in the code, where your workbook name is at the moment. (NB case sensitive.)
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows
Jonathan
I think we also need to stop the code when it gets to the last use cell in column "A", or it will be searching for ever!

Code:
Set rng = Range("a:a").End(xlUp)

So........

Code:
Private Sub Workbook_Open()
Dim ws As Worksheet
Dim c As Range, rng

Set rng = Range("a:a").End(xlUp)

For Each ws In ThisWorkbook.Worksheets
   
        Cells.Select
        Selection.EntireColumn.Hidden = False
        Selection.EntireRow.Hidden = False
       
        For Each c In rng
            If c.Value = "False" Then
            c.EntireRow.Hidden = True
            End If
        Next c
   
Next

End Sub
 

Forum statistics

Threads
1,141,591
Messages
5,707,277
Members
421,499
Latest member
Dpbj

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