If a worksheet is not present or blank, go to next iteration in a for loop

Question

Below is a code to compile data from a specific sheet "Repair Summary by Location" of multiple workbooks into the macrobook worksheet "Repair Summary".

There are workbooks that don't have any data on "Repair summary by Location". Macro should do nothing but skip to next workbook.

Additionally, if the sheet is present, BUT it is empty then also macro should do the same as above. Below is the code.

'set up the output workbook
 Set OutBook = ThisWorkbook 'Worksheets.Add
 Set OutSheet = OutBook.Sheets.Add
 OutSheet.Name = "Repair Summary"
 Set OutSheet = OutBook.Sheets(1)

'loop through all files
For FileIdx = 1 To TargetFiles.SelectedItems.Count

    'open the file and assign the workbook & worksheet
     Set DataBook = Workbooks.Open(TargetFiles.SelectedItems(FileIdx))
     Set DataSheet = DataBook.Sheets("Repair Summary by Location")

    'identify row/column boundaries
     LastDataRow = DataSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
     LastDataCol = DataSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    'if this is the first go-round, include the header
    If FileIdx = 1 Then
          Set DataRng = Range(DataSheet.Cells(HeaderRow, 1), DataSheet.Cells(LastDataRow, LastDataCol))
          Set OutRng = Range(OutSheet.Cells(HeaderRow, 1), OutSheet.Cells(LastDataRow, LastDataCol))
     'if this is NOT the first go-round, then skip the header
   Else
    Set DataRng = Range(DataSheet.Cells(HeaderRow, 1), DataSheet.Cells(LastDataRow, LastDataCol))
    Set OutRng = Range(OutSheet.Cells(LastOutRow + 2, 1), OutSheet.Cells(LastOutRow + 2 + LastDataRow, LastDataCol))
End If

'copy the data to the outbook
DataRng.Copy OutRng

'close the data book without saving
DataBook.Close False

'update the last outbook row
 LastOutRow = OutSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


  Next FileIdx

http://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists/6688482#6688482 — Tim Williams, Aug 07 '15 at 00:33
See @rory's answer in the above link. Use that with `Application.WorksheetFunction.CountA() ` and combine them... Just 4 lines of code... — Siddharth Rout, Aug 07 '15 at 03:26

score 1 · Answer 1 · answered Aug 07 '15 at 00:42

This is the alternative to @Tim's solution:

Public Function getSheet(ByVal wsName As String, Optional wb As Workbook = Nothing) As Worksheet
    Dim ws As Worksheet

    If Len(wsName) > 0 Then
        If wb Is Nothing Then Set wb = ActiveWorkbook
        For Each ws In wb.Worksheets
            If ws.Name = wsName Then
                Set getSheet = ws
                Exit Function
            End If
        Next
    End If
End Function

and to check that sheet exists and is not empty:

Dim ws As Worksheet

Set ws = getSheet("Repair Summary by Location")

If Not ws Is Nothing Then                       'validates if Worksheet exists
    If WorksheetFunction.CountA(ws) > 0 Then    'validates if Worksheet is not empty
        ...

@SiddharthRout - thanks; that part is actually from [one of your answers](http://stackoverflow.com/a/11169920/4914662) - you should have a lot more upvotes than you do! (I keep forgetting to upvote great answers) — paul bica, Aug 07 '15 at 14:46

score 1 · Answer 2 · answered Aug 07 '15 at 03:31

See @rory's answer in the above link. Use that with Application.WorksheetFunction.CountA() and combine them... Just 4 lines of code...

Further to my comment, here are the 4 lines of code

If Evaluate("ISREF('" & sName & "'!A1)") Then '<~~ If sheet exists
    If Application.WorksheetFunction.CountA(Sheets(sName).Cells) > 0 Then '<~~ If not empty
        '
        '~~> Your code
        '
    End If
End If

Dan Donoghue · Answer 3 · 2015-08-07T01:07:24.163

You can on error resume next but I would recommend against blanket solutions. I would use a loop once opening the book to look for the sheet using a function. Something like this:

Function FoundSheet(MySheetName as string) As Boolean
Dim WS as Worksheet
FoundSheet = False
For each WS in worksheets
    If WS.Name = MySheetName then
        FoundSheet = True
        Exit for
    End if
Next
End Function

This function returns a true or a false (As Boolean) and you would use this in your code like this:

If FoundSheet("YourSheetName") then 'Don't need = True or = False on the test as it is a boolean
    'Your code goes here Start with a test, select it then see if there is data
End if

I free hand typed the code so there may be a typo or two but I am sure you can debug it.

Here is a very crude example of how it can work (I ran this against a new workbook with Sheet1 and Sheet2 in there but no Sheet3):

Sub testFunc()
Dim X As Long
For X = 1 To 3
    MsgBox "Sheet" & X & " exists: " & FoundSheet("Sheet" & X)
Next
End Sub

It is giving me an error "expected end sub". Can you guide how I should include your code in my code. I am new to vba. — Chetan Chimate, Aug 07 '15 at 01:06

If a worksheet is not present or blank, go to next iteration in a for loop

3 Answers3