macro skips workbook if specific tab doesn\'t exist I have created a macro that
ID: 3560546 • Letter: M
Question
macro skips workbook if specific tab doesn't exist
I have created a macro that loops through all files in a subdirectory and pulls info I need. I would now like it to skip the workbook if it doesn't include a tab with a specific name. For example if tab "Risks Follow Up" doesn't exist close the workbook and go the next one in the subdirectory. Thank you!
Below is the current code.
Workbooks.Open "H:DanielRiskAssessment2012_MASTERFILE_results.xlsm"
strFolder = "H:DanielFiles"
strSubFolder = Dir(strFolder & "*", vbDirectory)
Do While Not strSubFolder = ""
Select Case strSubFolder
Case ".", ".."
' Current folder or parent folder - ignore
Case Else
' Add to collection
colSubFolders.Add Item:=strSubFolder, Key:=strSubFolder
End Select
' On to the next one
strSubFolder = Dir
Loop
' Loop through the collection
For Each varItem In colSubFolders
' Loop through Excel workbooks in subfolder
strFile = Dir(strFolder & varItem & "*.xls*")
Do While strFile <> ""
' Open workbook
Set wbk = Workbooks.Open(FileName:=strFolder & _
varItem & "" & strFile, AddToMRU:=False)
Code to pull info I need.
strFile = Dir
Loop
Next varItem
'End 'for each looking at sheet name
MsgBox NSheetCount & " worksheets copied from directory " & strFolder
End Sub
Explanation / Answer
Try putting this in right after you do the Set wbk = Workbooks.Open.... statement:
Dim testWS As Worksheet
On Error Resume Next
Set testWS = wbk.worksheets("Risks Follow Up")
If Err = 0 Then
On Error GoTo 0 ' reset error trapping
'the sheet does exist, do your stuff
Else
'the sheet did not exist
Err.Clear
On Error GoTo 0
End If
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.