Macro to determining ne if a worksheet exists

[BILLYBOB1972]

I have a master workbook which copies information from other workbooks. The macro I have opens the source document and pastes information from different worksheets into the master. The issue I have is the source workbooks do not always have all the worksheets so when I open the workbook I need to check if all the proposed worksheets exist before I copy the data and paste. Is there anyway of checking and if the sheet does not exist then do not try and copy the data.

Any help greatly appreciated.

 

[Fluff]

Something like

Sub Chk()

   Dim Wbk As Workbook
     
   Set Wbk = Workbooks.Open(Pth & Fname)
   If shtexists("Test", Wbk) Then
      ' Your code goes here
      ' Your code goes here
   End If
   Wbk.Close True

End Sub


Public Function shtexists(ShtName As String, Optional Wbk As Workbook) As Boolean
    If Wbk Is Nothing Then Set Wbk = ThisWorkbook
    On Error Resume Next
    shtexists = (LCase(Wbk.Sheets(ShtName).Name) = LCase(ShtName))
    On Error GoTo 0
End Function

 

[JLGWhiz]

This can maybe get you started.

Sub t()
dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet
Set wb1 = Workbooks(1)
Set sh1 = wb1.Sheets("Master")
Set wb2 = Workbooks.Open(TargetWorkbookFullName) 'Sustitute actual workbook path and name here.
On Error Resume Next
 Set sh2 = Sheets(TargetSheetName) 'Substitute actual sheet name here
    [COLOR=#FF0000]  If Err.Number = 0 Then
          'Do something with sh2
      End If[/COLOR]
On Error GoTo 0
End Sub

The On Error Resume Next statement will allow the code to move on if the sheet name in the Set sh2 statement does not exist in that workbook. The If Err.Number = 0 statement filters the Error register to allow action to be taken if the sheet does exist. If the error number is greater than zero, the code simply continues on.

 

[BILLYBOB1972]

Thanks for your help. Sorted

 

[JLGWhiz]

Thanks for your help. Sorted

Glad to assist,
regards, JLG