VBA If Worksheet Exists Go Forward in Code

Question

I didn't fount something useful for my problem although it seems to be a common problem. I have a simple old code of mine with a table of compnies data in one sheet. My code takes data based on a company name, finds a worksheet with that compny name inside the workbook and make few actions.

My problem starts when one of the sheets gets deleted and the the code stucks. I do in the code the same routine for every company, and I want it will fo to next company if it not finding worksheet with specific company name.

Can someone help with somthing that will work as an if statement?

Here is the start of the code and two companies for example - YEDIDIM & BEHIRIM. the else is the same:

Sub Calculation_of_Change()

Application.ScreenUpdating = False

'refresh 2 PivoTableS
Dim pivot As PivotTable
Set pivot = Worksheets("PIVOT").PivotTables("PivotTable1")
pivot.RefreshTable
Set pivot = Sheets("PIVOT (-)").PivotTables("PivotTable1")
pivot.RefreshTable

'we need to delete the old data and replace it with new data

'YEDIDIM
'first we will delete all old data
Sheets("YEDIDIM").Select
Range("A2", Range("A2").End(xlDown)).Select
Selection.EntireRow.Delete


'for each sheet with data we will filter the PIVOT table and paste the new data so we could calculate the stats we want
    Sheets("PIVOT").Activate
    ActiveSheet.PivotTables("PivotTable1").PivotFields("ùí úú îôòì"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("ùí úú îôòì").PivotFilters. _
        Add2 Type:=xlCaptionContains, Value1:="YEDIDIM"

    Range("A5", Range("A5").End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("YEDIDIM").Select
    Range("A2").PasteSpecial (xlPasteValues)


'BEHIRIM
'first we will delete all old data
Sheets("BEHIRIM").Select
Range("A2", Range("A2").End(xlDown)).Select
Selection.EntireRow.Delete


'for each sheet with data we will filter the PIVOT table and paste the new data so we could calculate the stats we want
Sheets("PIVOT").Activate
    ActiveSheet.PivotTables("PivotTable1").PivotFields("ùí úú îôòì"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("ùí úú îôòì").PivotFilters. _
        Add2 Type:=xlCaptionContains, Value1:="BEHIRIM"

    Range("A5", Range("A5").End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("BEHIRIM").Select
    Range("A2").PasteSpecial (xlPasteValues)

score 3 · Answer 1 · answered Oct 02 '18 at 08:44

3

This is the WorksheetExists() function that I usually use:

Function WorksheetExists(sheetName As String) As Boolean        
    WorksheetExists = Not WorksheetFunction.IsErr(Evaluate("'" & sheetName & "'!A1"))
End Function

For your goal, it can be used like this:

If Not WorksheetExists("NameOfTheWorksheet") Then Exit Sub

answered Oct 02 '18 at 08:44

Vityata

42,633
8
55
100

Thanks for posting. And if I just want to go to next compny and not exit sub, is there a way to do this? – Rafael Osipov Oct 02 '18 at 08:47
Another Q - If I call the companies wuth the specific name (YEDIDIM, BEHIRIM) without variable, the function will help me? – Rafael Osipov Oct 02 '18 at 08:48

score 1 · Accepted Answer · answered Oct 02 '18 at 08:47

I would suggest you to use 2 components for this:

A Boolean Variable
A Loop that will check every sheet before selecting

The idea is, after doing all operations, check every worksheet name until desired worksheet is found. If not found, then jump somewhere else.

So, probably I would do something like this, after the Pivot Table part:

'check if sheet exists
Dim wk As Worksheet
Dim wkFound As Boolean
wkFound = False
For Each wk In ThisWorkbook.Worksheets
    If wk.Name = "YEDIDIM" Then
        wkFound = True
        Exit For 'no need of checking rest of worksheets if found
    End If
Next wk

'If desired worksheet is found, then we execute code

If wkFound = True Then
    Range("A5", Range("A5").End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("YEDIDIM").Select
    Range("A2").PasteSpecial (xlPasteValues)
End If

About your code, I strongly recommend you to avoid using select. Check this link for more info:

Avoid SELECT in VBA

**`.Select`** is very bad advice. With VBA you never need to use that. essentially, your code within `If wkFound = True Then` can be written in a single statement without the use of `.Select` — Zac, Oct 02 '18 at 09:42
I agree with you @Zac. That's why I posted also a link related about *avoid Select in VBA*. In this answer, I just adapted the code of OP to what he/she was asking for. But as i said, I totally agree with you about. — Foxfire And Burns And Burns, Oct 02 '18 at 10:36

score 1 · Answer 3 · answered Oct 02 '18 at 09:02

1

You can do this with simple error handling.

Err.Clear    
On error resume next
Set ws = Worksheets("SomeName")
if err.Number > 0 then exit sub
On error goto 0    'disable error handling

answered Oct 02 '18 at 09:02

iDevlop

24,841
11
90
149

score -1 · Answer 4 · answered Oct 02 '18 at 08:37

-1

Maybe you can check all worksheets for the name. pseudo code

For Each ws In Worksheets
     if 'name' == ws.Name

answered Oct 02 '18 at 08:37

Rick de Gier

62
2

VBA used a singles equals for criteria comparison and double quotes for literal strings. – Oct 02 '18 at 08:46

VBA If Worksheet Exists Go Forward in Code

4 Answers4