Apply VBA Code to All Worksheets

[nryan]

Hello,

The help I've found on this subject has been great, but now I think my problem is more specific. I have the following code applied to one worksheet (right click sheet tab and click "view code") and it works very well:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

    If Not Intersect(Target, Range("C10:C100000")) Is Nothing Then

        With Target(1, -1)

            .Value = Date
            .EntireColumn.AutoFit

        End With
            
        With Target(1, 0)
                
            .Value = Time
            .EntireColumn.AutoFit
                
        End With

    End If


Columns("B").Hidden = True


    Dim MyRange As Range, c As Range
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Set MyRange = Range("A2:A4436")
    MyRange.EntireRow.Hidden = False
    
    For Each c In MyRange
        If IsDate(c.Value) And c.Value < Date Then
            c.EntireRow.Hidden = True
        End If

    Next
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic


End Sub

Now I tried to apply this code to ALL worksheets by putting it in ThisWorkbook in the VB editor (right click "ThisWorkbook" in the VB editor and click "view code"). All I changed was the first line of the code. Here is that code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

    If Not Intersect(Target, Range("C10:C100000")) Is Nothing Then

        With Target(1, -1)

            .Value = Date
            .EntireColumn.AutoFit

        End With
            
        With Target(1, 0)
                
            .Value = Time
            .EntireColumn.AutoFit
                
        End With

    End If


Columns("B").Hidden = True


    Dim MyRange As Range, c As Range
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Set MyRange = Range("A2:A4436")
    MyRange.EntireRow.Hidden = False
    
    For Each c In MyRange
        If IsDate(c.Value) And c.Value < Date Then
            c.EntireRow.Hidden = True
        End If

    Next
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic


End Sub

The problem is the If statement only works on the first worksheet. This statement inputs the date and time into columns "A" and "B" when column "C" is changed. The dates and times are not appearing in their columns for all sheets (except Sheet1). The problem must lie with either the If statement or the two With statements. Anyone have a solution?

Thanks very much.
-Nick

 

[JLGWhiz]

The only advantage to putting the code into the ThisWorkbook code module is that you don't have to have the same code in each worksheet module. But you do still have to make individual changes to each separate sheet for the code to affect that sheet. The code will not apply to all sheets by making a change to only sheet 1. You will need to use a loop if you want to do all sheets in one transaction.

 

[nryan]

I feel silly. The code works fine. I forgot to change C10 back to C2 for the start of the Range. I was typing stuff into C2, C3, C4, etc. for all worksheets (except Sheet1 for some reason) and not getting anything of course because the code doesn't start inputting dates and times until row 10.

Thanks anyways JLGWhiz.

 

[JLGWhiz]

I feel silly. The code works fine. I forgot to change C10 back to C2 for the start of the Range. I was typing stuff into C2, C3, C4, etc. for all worksheets (except Sheet1 for some reason) and not getting anything of course because the code doesn't start inputting dates and times until row 10.

Thanks anyways JLGWhiz.

Been there, done that!
Regards, JLG