Macro to determining ne if a worksheet exists

BILLYBOB1972

New Member
Joined
Jan 7, 2016
Messages
22
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.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,940
Office Version
  1. 365
Platform
  1. Windows
Something like
Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
This can maybe get you started.
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,532
Messages
5,596,721
Members
414,092
Latest member
Eran2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top