Loop through each worksheet

[noora19]

I am trying to loop the code below through all worksheets within a workbook, but so far have been unsuccesful. The code between the 'for each' and 'next' statement is only a small part of my actual code, but I am not understanding why it is only running the code for the active worksheet, not all worksheets. What am I missing?

Sub AllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets

Range("C3:I3").Select
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

GoToNextSheet:
Next ws

End Sub

 

[Manohar M G]

Hi...

Can you tell what is the problem..? What you want to get the output instead of writing code.. it will be helpful.

 

[noora19]

Hello--thank you for the quick reply. I am trying to enter text to a specfic range of cells in each worksheet of a workbook as well as format the same range by adding borders etc.. The For Each Next statement I have only produces this output in the first worksheet and does not loop through the rest of the sheets.

Thanks

 

[Misca]

Your code is missing the ws reference. Try:

Sub AllSheets()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

    With ws
        Application.CutCopyMode = False
        With .Range("C3:I3")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
        End With
    End With
    
GoToNextSheet:
Next ws

End Sub

I added the missing ws reference + deleted the unnecessary .Select part.

 

[AMAS]

Maybe try this also:

Sub AllSheets()

Dim x As Integer

wsCount = ActiveWorkbook.Worksheets.Count

For x = 1 To wsCount

    Sheets(x).Range("C3:I3").MergeCells = True

Next x

End Sub

 

[AMAS]

Here is a shorter version:

Sub AllSheets()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    ws.Range("C3:I3").MergeCells = True
Next ws

End Sub

AMAS

 

[noora19]

Thanks all! I ended up using the code AMAS provided --it worked well!

 

[AMAS]

Thanks for the confirmation. Glad to be of assistance.

AMAS

 

[noora19]

One more question-- I have a bunch of other code (additional formatting code) that I want to add to this. So how would I combine? Below are a few pieces I have from using the macro recorder:

Range("C3:I3").Select
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection.Font
.Color = -6684673
.TintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10092543
.TintAndShade = 0
.PatternTintAndShade = 0
End With


So I replaced the first part of the code with

ws.Range("C3:I3").MergeCells = True

But now I am trying to replace the bordering and color formatting. Would I replace each 'selection' with

'ws.Range("C3:I3").' ?

That didn't seem to work. Do I need all the with statements for this? What is the best way for me to make this work? Thanks again..

 

[Misca]

Try:

Dim WS As Worksheet

For Each WS In Worksheets
    With WS.Range("C3:I3")
        .MergeCells = True
                
'Sets the border settings all at once:
        With .Borders
        .LineStyle = xlContinuous
        .Weight = xlMedium
        End With
        
'Removes the unwanted borders:
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlInsideVertical).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
        
        With .Font
            .Color = -6684673
        End With
        
        With .Interior
            .Pattern = xlSolid
            .Color = 10092543
        End With
    End With
Next WS