Adding private sub worksheet code to all existing sheets in a workbook

[suzeeq]

Relatively new to VBA but figuring it out as I go. This forum is an AMAZING resource, so thank you!

I have a piece of code that works on a worksheet if I right click on the worksheet tab, view code, and paste it in. However, I was wondering if there was a way to get this code to work on all worksheets in a workbook without having to go through this copy/paste step. Basically, it unhides rows in a certain range if data is filled in the row above it. This is more of a housekeeping thing than a necessity.

I'm doing a major overhaul of a workbook I made (for myself) that is being used by others. Now 100+ workbooks are already populated with data so all of the coding that I've been working on (learning) to date has been for a single run on multiple workbooks to incorporate some changes that I've been asked to make. This is different and I'm stuck. I've found other coding that is similar but requires manual intervention to run. Please note - I am an accountant, not a programmer or IT person - worksheets are an amazing tool and have become extraordinarily powerful since the Lotus123 days when I was first introduced. I don't want to chase my tail trying to figure this out if it simply isn't possible.

1) Can I modify the below code as a module to run on the active worksheet in a workbook?
2) Can I insert this code into multiple workbooks without opening each one up and importing it?

Thanks in advance for any ideas

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Dim rng As Range
Set rng = Range("a77:d105")
    If Not Intersect(rng, Target) Is Nothing Then
        If Target.Value <> "" Then
            Target.Offset(1).EntireRow.Hidden = False
        Else
        End If
    End If
Application.EnableEvents = True
End Sub

 

[offthelip]

You can get it to run on all worksheets in a workbook by moving it to the workbook sheet change event: open the VBA project right click on "ThisWorkbook" select view code nad past this code into the window:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Dim rng As Range
Set rng = Range("a77:d105")
    If Not Intersect(rng, Target) Is Nothing Then
        If Target.Value <> "" Then
            Target.Offset(1).EntireRow.Hidden = False
        Else
        End If
    End If
Application.EnableEvents = True
End Sub

 

[suzeeq]

You can get it to run on all worksheets in a workbook by moving it to the workbook sheet change event: open the VBA project right click on "ThisWorkbook" select view code nad past this code into the window:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Dim rng As Range
Set rng = Range("a77:d105")
    If Not Intersect(rng, Target) Is Nothing Then
        If Target.Value <> "" Then
            Target.Offset(1).EntireRow.Hidden = False
        Else
        End If
    End If
Application.EnableEvents = True
End Sub

Thank you!

 

[suzeeq]

@offthelip

This code works on all worksheets in the workbook I entered it in. Thank you

If I needed to add this 'workbook sheet change event code' to 100 workbooks, can I do it via additional VBA code? I am not necessarily asking for the code, just if it's possible. I appreciate the awesome help you all give but I want to learn/understand this so I can replicate it if needed.

If I create a worksheet with this code and copy it into all of the workbooks will it maintain the code as a workbook sheet change event for all the sheets in the workbooks?

If this isn't possible, could I create a workbook with this code in it and merge it with the 100 workbooks?

 

[offthelip]

It is possible to edit the VBA project using VBa. however it is a lot more complicated and is something I have never done ( only because I have never needed to) . However Chip Pearson who has brilliant website describes how to do it see:
Programming In The VBA Editor
Note I have often wanted to do a similar thing, however instead of triggering the macro off an event I put the macor in my personal.xlsb file and then add an icon to the quick access toolbar, which makes it available in all workbboks that I open. So it depends on whether other people need to run the macro

 

[suzeeq]

It is possible to edit the VBA project using VBa. however it is a lot more complicated and is something I have never done ( only because I have never needed to) . However Chip Pearson who has brilliant website describes how to do it see:
Programming In The VBA Editor
Note I have often wanted to do a similar thing, however instead of triggering the macro off an event I put the macor in my personal.xlsb file and then add an icon to the quick access toolbar, which makes it available in all workbboks that I open. So it depends on whether other people need to run the macro

Unfortunately, I will need the macro to run on the workbooks independently of my computer/network environment. I have everything else for the updates in a personal.xlsb file but I want this one to live in every workbook. Thank you for the link!