Hey all,
Need help with my workbook.
In the summary sheet, columns A and B are players names, which are retrived from a list
I have several worksheets named after some players names,
when I enter in column A the name "John" and in column B the name "Joe" for example, and i fill the row with information, and I type BAB in the L column, this code automatically copies the data to worksheet named "John" and to worksheet named "Joe" and makes some adjustments.
The problem is I need help to bypass the error generated when I enter a name in column A or B that does not have a worksheet named after it.
I need to edit the code to be able to check wether the worksheet of the player exists, if true then continue the code, if not then display a message box saying that the worksheet "player name" does not exist.
Thanks
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myDestSheet As String, myDestSheet2 As String, destRow As Long, destRow2 As Long
With ThisWorkbook
If Target.Cells.Count = 1 And Target.Column = 12 And Target = "BAB"
myDestSheet = Target.Offset(, -11)
destRow = .Sheets(myDestSheet).Cells(Rows.Count, "a").End(xlUp).Row + 1
myDestSheet2 = Target.Offset(, -10)
destRow2 = .Sheets(myDestSheet2).Cells(Rows.Count, "a").End(xlUp).Row + 1
Application.EnableEvents = False
Target.Parent.Cells(Target.Row, 5).Resize(, 7).Copy .Sheets(myDestSheet).Cells(destRow, 5)
Target.Parent.Cells(Target.Row, 5).Resize(, 7).Copy .Sheets(myDestSheet2).Cells(destRow2, 5)
.Sheets(myDestSheet).Cells(destRow, 1) = Target.Offset(, -9)
.Sheets(myDestSheet).Cells(destRow, 2) = Target.Offset(, -8)
.Sheets(myDestSheet).Cells(destRow, 3) = "GO"
.Sheets(myDestSheet).Cells(destRow, 4) = Target.Offset(, -10)
.Sheets(myDestSheet2).Cells(destRow2, 1) = Target.Offset(, -9)
.Sheets(myDestSheet2).Cells(destRow2, 2) = Target.Offset(, -8)
.Sheets(myDestSheet2).Cells(destRow2, 3) = "NOGO"
.Sheets(myDestSheet2).Cells(destRow2, 4) = Target.Offset(, -11)
Application.EnableEvents = True
End If
End With
End Sub
Bookmarks