Generating all macros to work across worksheets when opening

[Fmedia]

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

 

[sykes]

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:

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

 

[Fmedia]

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]

Ah, Ok!

Always assuming your code works "as is", then to out it into my idea, it would look thus:

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]

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]

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:

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]

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!

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

So........

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