CODEWITHSUNDEEP
excelvba
Rafael Osipov
Rafael Osipov

Reputation: 740

VBA If Worksheet Exists Go Forward in Code

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)

Upvotes: 0

Views: 64

Answers (4)

iDevlop
iDevlop

Reputation: 25252

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

Upvotes: 1

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

I would suggest you to use 2 components for this:

  1. A Boolean Variable
  2. 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

Upvotes: 1

Vityata
Vityata

Reputation: 43575

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

Upvotes: 3

Rick de Gier
Rick de Gier

Reputation: 62

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

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

Upvotes: -1

Related Questions