Excel VBA - How to Exit Code if Sheet Not Found

Question

My code is to merge multiple sheet from a folder. I achieved the first requirement which merge all the sheet1 of target workbooks. But now, i want to merge the 4th sheet of the target workbooks. before that i need to check whether the sheet is exist or not. if exist the code should merge the 4th sheets. This one also i managed to achieved. However if the the 4th sheet not exist the code should do nothing. This part im still stuck. below are the code.

Set shtDest = ActiveWorkbook.Sheets("MS2")
Filename = Dir(path & "\*.xls", vbNormal)
If Len(Filename) = 0 Then Exit Sub
Do Until Filename = vbNullString
    If Not Filename = ThisWB Then
        Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
        For i = 1 To Worksheets.Count
        If Worksheets(i).Name = "PID2" Then
        Wkb.Sheets(4).Activate
        Set CopyRng = Wkb.Sheets(4).Range(Cells(RowofCopySheet, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count))
        Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row)
        CopyRng.Copy Dest
        Wkb.Close False
        "ElseIf Worksheets(i).Name <> "PID2" Then"
        "Wkb.Close False"
        "Exit Sub"
        End If
        Next i
    End If

    Filename = Dir()
Loop

The elseif part shouldn't be within quotes, just else will do and no need of Exit Sub within that else part, the loop will take care of checking the next file. — Karpak
– Karpak, Commented Dec 22, 2016 at 3:51
is the 4th sheet named "PID2"? in that case the line Wkb.Sheets(4).Activate should change to Worksheets(i).Activate — nightcrawler23
– nightcrawler23, Commented Dec 22, 2016 at 3:58

nightcrawler23 · Accepted Answer · 2016-12-22 04:01:06Z

1

Assuming PID2 is the 4th sheet you want to copy, if it exists

Sub t()
    Set shtDest = ActiveWorkbook.Sheets("MS2")
    Filename = Dir(Path & "\*.xls", vbNormal)
    If Len(Filename) = 0 Then Exit Sub

    Do Until Filename = vbNullString
        If Not Filename = ThisWB Then
            Set Wkb = Workbooks.Open(Filename:=Path & "\" & Filename)

            For i = 1 To Worksheets.Count
                If Worksheets(i).Name = "PID2" Then
                    Worksheets(i).Activate
                    Set CopyRng = Worksheets(i).Range(Cells(RowofCopySheet, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count))
                    Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row)
                    CopyRng.Copy Dest
                End If
            Next i

            Wkb.Close False
        End If

        Filename = Dir()
    Loop
End Sub

answered Dec 22, 2016 at 4:01

nightcrawler23

2,0761 gold badge14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BruceWayne Over a year ago

You should also add Worksheets(I) before the Cells() usage to be safe in Set CopyRng... also you're using the active sheet to determine the number of cells. Should that also be the Worksheet(I)?

nightcrawler23 Over a year ago

Yes. But i didn't want to change much of the original code. That is why I also left the .Activate part.

Ramandeep Singh · Accepted Answer · 2016-12-22 05:47:32Z

PFA for the required code, I have made some modification in the code.

    Set shtDest = ActiveWorkbook.Sheets("MS2")

Filename = Dir(Path & "\*.xls", vbNormal)

If Len(Filename) = 0 Then Exit Sub

Do Until Filename = vbNullString

    If Not Filename = ThisWB Then

        Set Wkb = Workbooks.Open(Filename:=Path & "\" & Filename)

        For i = 1 To Worksheets.Count

            If Worksheets(i).Name = "PID2" Then

                Wkb.Sheets(i).Activate
                Set CopyRng = Range(Cells(RowofCopySheet, 1), ActiveCell.SpecialCells(xlCellTypeLastCell))
                Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row)
                CopyRng.Copy Dest

                Exit For

            End If
        Next i

        Wkb.Close False

    End If

    Filename = Dir()
Loop

KyloRen · Accepted Answer · 2016-12-22 04:10:08Z

0

You need to specify some criteria and then exit after that,

ie,

If something = <criteria> Then
   goto exitsub
end if

exitsub:

This will jump to exitsub using the : at the end of the string you specify exitsub: You could make it anything you wanted, eg goToEndOfSub:

If something = <criteria> Then
      goto goToEndOfSub
end if

goToEndOfSub:

Also you can use the Exit Statement, in your case a do loop.

Exit Do

edited Dec 22, 2016 at 4:10

answered Dec 22, 2016 at 4:02

KyloRen

2,7715 gold badges33 silver badges64 bronze badges

2 Comments

logicOnAbstractions Over a year ago

Seems needlessly elaborated - you could just put "Exit Sub" where relevent.

KyloRen Over a year ago

@Francky_V, maybe, but better to thoroughly explain for someone else who might stumble upon this question in the future.

Collectives™ on Stack Overflow

Excel VBA - How to Exit Code if Sheet Not Found

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related