CODEWITHSUNDEEP
excelvba
DA69
DA69

Reputation: 91

Check other workbook for sheet name before progressing

I have this code that works great.

Except when the other sheet in the list does not have the assigned sheet name it does nothing. So I wanted to add a code that would have a popup saying the "sheet name does not exist on that workbook".

I have tried as many codes as I can but none seem to work. This last one works BUT it has a popup for every sheet that is in that workbook that is not the sheet I am looking for.

How can I edit this so that when clicked, the code will search the other workbook, determine if the sheet is there in as many sheets and only have 1 popup saying that it isn't?

Thank you

Private Sub CopyPasteButton_Click()
    ActiveSheet.Unprotect Password:=PSWD
    Dim mySheet As Worksheet, otherSheet As Worksheet
    Dim ws As Worksheet

    On Error GoTo exit_err

    Application.DisplayAlerts = False

    Set mySheet = ThisWorkbook.Sheets("Info")


    For Each ws In Workbooks(Me.ListBox1.Value).Worksheets
    If ws.Name = "This is It" Then

        Set otherSheet = Workbooks(Me.ListBox1.Value).Sheets("This is It")

        If otherSheet.Range("AN1") >= 148 Then

            mySheet.Range("A50:J57").Copy
        otherSheet.Range("A5:J12").PasteSpecial xlPasteValuesAndNumberFormats

            mySheet.Range("M6:N6").Copy
        otherSheet.Range("Q19:R19").PasteSpecial xlPasteValuesAndNumberFormats

        Else
           MsgBox "Wrong Sheet Version"
        End If

        Else
           MsgBox "Sheet Does not Exist"
    End If
    Next ws

exit_err:
    mySheet.Protect Password:=PSWD
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
End Sub

Function WorksheetExists(ByValWorksheetName As String) As Boolean
Dim Sht As Worksheet

For Each Sht In Workbooks(Me.ListBox1.Value).Worksheets
If Application.Proper(Sht.Name) = Application.Proper(WorksheetName) Then
WorksheetExists = True
Exit Function
End If
Next Sht
WorksheetExists = False
End Function

Upvotes: 1

Views: 62

Answers (1)

Rachel Hettinger
Rachel Hettinger

Reputation: 8442

Don't loop through the sheets, just try to set a reference to the sheet and trap the error in case it doesn't exist.

Private Sub CopyPasteButton_Click()
ActiveSheet.Unprotect Password:=pswd
Dim mySheet As Worksheet, otherSheet As Worksheet
Dim ws As Worksheet

On Error GoTo exit_err

Application.DisplayAlerts = False

Set mySheet = ThisWorkbook.Sheets("Info")

On Error Resume Next
Set otherSheet = Workbooks(Me.ListBox1.Value).Worksheets("This is It")
On Error GoTo 0
If Not otherSheet Is Nothing Then

    If otherSheet.Range("AN1") >= 148 Then

        mySheet.Range("A50:J57").Copy
        otherSheet.Range("A5:J12").PasteSpecial xlPasteValuesAndNumberFormats

        mySheet.Range("M6:N6").Copy
        otherSheet.Range("Q19:R19").PasteSpecial xlPasteValuesAndNumberFormats

    Else
       MsgBox "Wrong Sheet Version"
    End If

Else
   MsgBox "Sheet Does not Exist"
End If

exit_err:
    mySheet.Protect Password:=pswd
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
End Sub

Upvotes: 2

Related Questions