VBA to update all worksheets except one

[cab401]

I need to clear specific cells & unmerge 2 cells on every worksheet except Sheet1.

Below is the code I'm using, however, after updating all pages appropriately, it updates Sheet1 as well. Help! VBA newbie . . .

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet1" Then

Range("G5").Select
Selection.ClearContents
Range("I8:J8").Select
Selection.ClearContents
Range("D14:E14").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
Range("F4").Select
End If
Next ws

End Sub

 

[Joe4]

The issue is that looping through the names of the sheets does not actually activate them.
So all ranges that do have a sheet component default to the active sheet.

So, you need to do one of three things after your "IF" statement.

1. Select/activate the sheet:

ws.Activate

2. Use a sheet reference in front of each range, i.e.

[COLOR=#333333]ws.Range("G5").ClearContents[/COLOR]

3. Use a With statement, and put periods if front of all your range to indicate that it applies to that sheet, i.e.

With ws
[COLOR=#333333]    .Range("G5").ClearContents
[/COLOR]    ...
End With

Also, you can get rid of most of your "Select" statements. It is not necessary to select ranges in order to work with them. Most lines that end with "Select" and the next row begins with "Selection" can be combined into one line.

Try this updated code:

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then
            With ws
                .Range("G5").ClearContents
                .Range("I8:J8").ClearContents
                With .Range("D14:E14")
                    .HorizontalAlignment = xlGeneral
                    .VerticalAlignment = xlBottom
                    .WrapText = False
                    .Orientation = 0
                    .AddIndent = False
                    .IndentLevel = 0
                    .ShrinkToFit = False
                    .ReadingOrder = xlContext
                    .MergeCells = True
                End With
                .Range("D14:E14").UnMerge
            End With
        End If
    Next ws

 

[cab401]

This successfully unmerged the 2 cells but did not clear the contents from G5 or I8:J8.

 

[Joe4]

This successfully unmerged the 2 cells but did not clear the contents from G5 or I8:J8.

It did for me.

Did you copy my code, EXACTLY as I have written it?
What is the name of the Module where you have this VBA code in?
Do you have any other VBA code in your workbook that may be interfering with it?

 

[cab401]

Joe, sorry for the confusion - I figured out what I did wrong. Your code is working PERFECTLY!

Thank you very much!!

 

[Joe4]

You are welcome.