VBA code to check if sheet exist, if so, then run, else move to next sheet

[rudolfweyers]

Good day

Currently I have a module that will run through a workbook, and on each sheet change a range of cells from general to number.

It works well if all the sheets are in the workbook. Some projects wont have all the sheets, so my code then gets stuck as the sheet doesn't exist.

Im thinking of using the an If statement to check if the sheet exist, if so, run the code to change the cells, else move on to check if the next sheet exist.

So far I haven't been able to figure it out.

Here is a piece of the code for a couple of the sheets.

Sub Selection()
'
' Selection Macro
'

'
Sheets("BOQ_Door Handle Schedule").Select
With Range("C2:D500")
.NumberFormat = "General"
.Value = .Value
End With

Sheets("BOQ_Earthwork Cut & Fill").Select
With Range("E2:F500")
.NumberFormat = "General"
.Value = .Value
End With

Sheets("BOQ_Electrical Installation Sch").Select
With Range("C3:D1000")
.NumberFormat = "General"
.Value = .Value
End With

End Sub

 

[dmt32]

Hi,
one way maybe

Sub Selection()
    Dim SheetName   As String
    Dim i           As Long
    For i = 1 To 3
    
        SheetName = Choose(i, "BOQ_Door Handle Schedule", "BOQ_Earthwork Cut & Fill", _
                               "BOQ_Electrical Installation Sch")
                               
        If Evaluate("ISREF('" & SheetName & "'!A1)") Then
            With Worksheets(SheetName).Range(Choose(i, "C2:D500", "E2:F500", "C3:D1000"))
                .NumberFormat = "General"
                .Value = .Value
            End With
        End If
        
    Next i
    
End Sub

Dave

 

[rudolfweyers]

Hi,
one way maybe

Sub Selection()
    Dim SheetName   As String
    Dim i           As Long
    For i = 1 To 3
   
        SheetName = Choose(i, "BOQ_Door Handle Schedule", "BOQ_Earthwork Cut & Fill", _
                               "BOQ_Electrical Installation Sch")
                              
        If Evaluate("ISREF('" & SheetName & "'!A1)") Then
            With Worksheets(SheetName).Range(Choose(i, "C2:D500", "E2:F500", "C3:D1000"))
                .NumberFormat = "General"
                .Value = .Value
            End With
        End If
       
    Next i
   
End Sub

Dave

Thanks Dave, I think I understand what you are getting at. Only problem is, I've shown 3 of the about 70, and increasing sheets. So it could become rather complicated using you method

Thanks for helping

 

[dmt32]

Thanks Dave, I think I understand what you are getting at. Only problem is, I've shown 3 of the about 70, and increasing sheets. So it could become rather complicated using you method

Thanks for helping

Always helpful if you share such information when posting.
If your requirement is to just loop all sheets in workbook & apply the format code, then use the Sheets index property in a loop rather than the sheets name


Dave

 

[Fluff]

You could always do it sheet by sheet, if you prefer

If Evaluate("isref('BOQ_Door Handle Schedule!A1)") Then
   With Sheets("BOQ_Door Handle Schedule").Range("C2:D500")
      .NumberFormat = "General"
      .Value = .Value
   End With
End If
If Evaluate("isref('BOQ_Earthwork Cut & Fill!A1)") Then
   With Sheets("BOQ_Earthwork Cut & Fill").Range("E2:F500")
      .NumberFormat = "General"
      .Value = .Value
   End With
End If

 

[rudolfweyers]

You could always do it sheet by sheet, if you prefer

If Evaluate("isref('BOQ_Door Handle Schedule!A1)") Then
   With Sheets("BOQ_Door Handle Schedule").Range("C2:D500")
      .NumberFormat = "General"
      .Value = .Value
   End With
End If
If Evaluate("isref('BOQ_Earthwork Cut & Fill!A1)") Then
   With Sheets("BOQ_Earthwork Cut & Fill").Range("E2:F500")
      .NumberFormat = "General"
      .Value = .Value
   End With
End If

Thanks, I have just tried that, comes up with a error 13 - type mismatch

 

[rudolfweyers]

Thanks, I have just tried that, comes up with a error 13 - type mismatch

Found the error. There was a ' missing before !A1

Its working now

Here is a segment of the working code

If Evaluate("ISREF('BOQ_Joinery Schedule'!A1)") Then
With Sheets("BOQ_Joinery Schedule").Range("C2:D500")
.NumberFormat = "General"
.Value = .Value
End With
End If

 

[Fluff]

Glad we could help & thanks for the feedback.