When using multiple With End With in the same Sub

[JeffGrant]

Hi All,

I am in the process of cleaning up my code and cant seem to find an answer to this one.

Often I do.....

Sub ABC
With Sheet1
End With

With Sheet2
End With

With Sheet3
End With
End Sub

All I ever see is:

Dim ws as worksheet
Set ws = Sheeet1

With ws
End With

My questions are:
1, To make my code "cleaner" should I be changing the Set statement every time I change the With Statement?
2. With a Nested With statement, should I also be adding the Set statement?

My code works fine now, however, I do suspect that the structure of code is the cause of the occasional runtime errors that I get.

As always, thanks for your help.

 

[Skyybot]

I don't know if there is much difference, but you could always Loop if you're working with consecutive Sheets.

Sub ABC()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    With ws
        'Some stuff
    End With
Next ws
End Sub

 

[Micron]

I don't understand either question.
1) Your code (Often I do.....) doesn't use any Set statements.
2) Your code does not contain any nested With blocks. A nested With block would look like (please don't focus on the validity of the property references I used as I'm new to Excel vba and am writing from memory, as bad as that often is)

With Sheet1
   .Range("A1") = "foobar"
      With .Range("A1")
          .Font.Color = vbRed
          .Interior.Color = vbYellow
     End With
End With