How to use VBA Code to auto populate with condition in specific sheet

[ray.kanata]

Hi,
I need help in modifying code below. Currently this code work fine if I paste it in "ThisWorkBook" but for some reason I want to paste in a specific work sheet of workbook.
I tried by pasting it in specific worksheet but it didn't work. I think it needs some modification to work properly if pasted in work sheet.

The code is:

Private Const kBwb = "PROJECT SCREENING LIST"
Private Sub SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim r As Long
Dim wbA As Workbook, wbB As Workbook
Dim vAns
Dim sSht As String
Dim bIsRun As Boolean

   'decision change
If Target.Column = 12 And Not bIsRun Then
   bIsRun = True
   If IsWbBOpen() Then
     Set wbA = ActiveWorkbook
     Set wbB = Workbooks(kBwb & ".xlsx")

     r = ActiveCell.Row
     vAns = UCase(ActiveCell.Value)
     sSht = vAns & "_Sheet"
     Range("B" & r & ":K" & r).Select
     Selection.Copy
     
     wbB.Activate              'B workbook
     wbB.Sheets(sSht).Activate
     
     Range("B6").Select
     If ActiveCell.Offset(1, 0) <> "" Then Selection.End(xlDown).Select
     ActiveCell.Offset(1, 0).Select   'next empty cell
     r = ActiveCell.Row

     wbB.ActiveSheet.Paste                  'paste
     Application.CutCopyMode = False
 
                                    'remove unwanted cells
     Select Case vAns
        Case "YES"
           Range("L" & r).Value = ""
        Case "NO"
           Range("L" & r).Value = ""
        Case "HOLD"
           Range("L" & r).Value = ""
     End Select
     wbA.Activate
   Else
      MsgBox "B workbook is not open", vbCritical, "Missing workbook"
   End If

   bIsRun = False
   Set wbA = Nothing
   Set wbB = Nothing
End If
End Sub

Private Function IsWbBOpen() As Boolean
Dim wb As Workbook
Dim bFound As Boolean

For Each wb In Workbooks
    If InStr(wb.Name, kBwb) > 0 Then
       bFound = True
       GoTo endit
    End If
Next

endit:
IsWbBOpen = bFound
Set wb = Nothing
End Function

[Kenneth Hobson]

If you don't know the reason you are doing it, then we wouldn't.

Guessing that you might want to change the 2nd line to?

Private Sub Worksheet_Change(ByVal Target As Range)

You did not set a value for bIsRun so an IF for it won't be of much use.

[ray.kanata]

Thanks Kenneth!

I tried but gave me an error.

Please find attached files.
"WorkBook_A" contains VB codes for few different functions.
Code pasted in Thisworkbook auto increment project number and clear the scoring once submitted;
Code pasted in Module 1 save file in excel & PDF formats at desktop and delete the code pasted in Thisworbook once submitted;
Code pasted in Sheet named "Decision Point" is for populating Sheets in WorkBook_B based on selection (Yes, No, On-hold)

Code pasted in Sheet named "Decision Point" is giving me an error.

I need help to fix it because my knowledge of VB code is very week.

Thanks in advance to all

WorkBook_A.xlsm
WorkBook_B.xlsx