Help with running macro on specific worksheets in workbook

[Swinn24]

Hi everyone, I have searched through this forum to help myself and have learned a lot, but I am still experiencing difficulty getting my VBA code to work correctly. I have written code for ThisWorkbook and Module1 for the macro to run only when a worksheet is active and a specific cell is changed. I have two worksheets that the macro is not needed for, but can not hide them. Could you please review my code and let me know what edits are needed? I'll show my original code below that does not include my failed attempts.

Worksheets needing macro:
Test1
Test2
Test3
Test4
Test5
Test6

Worksheets not needing macro:
Scope
Summary

ThisWorkbook

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

Dim wsa As Worksheet
Set wsa = ActiveSheet


Dim TP As Integer
Dim SS As Integer

TP = wsa.Range("A:A").Find("Population:").Row
SS = wsa.Range("A:A").Find("Sample:").Row



On Error Resume Next
If Target.Address = ActiveSheet.Range("B" & TP).Address Or Target.Address = ActiveSheet.Range("B" & SS).Address Then Macro1
On Error GoTo 0

End Sub

Module1

Sub Macro1()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Dim wsa As Worksheet
Set wsa = ActiveSheet

Dim rrange As Range
Set rrange = wsa.Range("A:A").Find("Result Matrix:")

Dim TP As Integer
Dim SS As Integer

TP = wsa.Range("A:A").Find("Population:").Row
SS = wsa.Range("A:A").Find("Sample:").Row


Dim row_count As Integer
Dim row_need As Integer

row_need = wsa.Range("B" & SS).Value

If row_need < 10 Then row_need = 10
If row_need > 200 Then row_need = 100

row_count = rrange.Row - 31 + (24 - SS)

Do Until row_count = row_need
Set rrange = wsa.Range("A:A").Find("Result Matrix:")
row_count = rrange.Row - 31 + (24 - SS)


    If row_count < row_need Then
    wsa.Cells(rrange(-3).Row, 1).EntireRow.Copy
    wsa.Cells(rrange(-3).Row, 1).Insert Shift:=xlDown
    End If
    
    If row_count > row_need Then
    wsa.Cells(rrange(-3).Row, 1).EntireRow.Delete
    End If

Loop

Application.CutCopyMode = False

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic


End Sub

 

[offthelip]

Add these three lies at the top of your workbook change module to detect which sheets have changed:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Tname As String
Tname = Sh.Name
If Left(Tname, 4) = "Test" Then Else Exit Sub

Dim wsa As Worksheet
Set wsa = ActiveSheet


Dim TP As Integer
Dim SS As Integer

TP = wsa.Range("A:A").Find("Population:").Row
SS = wsa.Range("A:A").Find("Sample:").Row



On Error Resume Next
If Target.Address = ActiveSheet.Range("B" & TP).Address Or Target.Address = ActiveSheet.Range("B" & SS).Address Then Macro1
On Error GoTo 0

End Sub