Results 1 to 10 of 10

Need to check if worksheet exists and execute code

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-08-2012
    Location
    beirut
    MS-Off Ver
    Excel 2010
    Posts
    102

    Need to check if worksheet exists and execute code

    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
    Last edited by Cutter; 09-23-2012 at 10:34 AM. Reason: Added code tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1