Format All Worksheets but formatting the same worksheet

[nayseem]

Good morning,

I am a complete newbie to Excel VBA but have been watching YouTube Videos and reading these forums to figure things out but I need your help as I am stuck...

I created the following macro and would like it to format all the worksheets in my spreadsheet (about 40 of them). However, the code below is formatting one of the worksheets and inserting 11 rows - not sure where the problem is.

The ideal goal would be to have this macro format all macros except my first three worksheets as they are my summary, instructions and initial macro pages.

Any suggestions? I would be happy to clarify the needs as well.

Thanks in advance!



Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+L
'


Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets


Columns("A:O").Select
Columns("A:O").EntireColumn.AutoFit
Columns("M:N").Select
Selection.ColumnWidth = 12.71
Rows("1:1").EntireRow.AutoFit
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1:O1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("F1").Select
ActiveCell.FormulaR1C1 = "Opportunity Report"
Range("H1").Select
ActiveCell.FormulaR1C1 = "=TODAY()+1"
Range("H1").Select
Columns("H:H").Select
Range("H2:H40").Activate
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""Due"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
Next ws
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False



End Sub

 

[RCBricker]

WTTBs

This first post is to help you out in the future.

when posting code please use code tags

remove the spaces

[ code ]

[ /code ]

rich

 

[Joe4]

Welcome to the Board!

The problem is, though you appear to be looping through your worksheets, you really aren't, as you aren't selecting/activating each worksheet. So if you have 11 sheets, you are really running the code 11 times on the same sheet (whatever is the active sheet when your run the macro).

Here is how you can loop through all your sheets, starting with the 4th sheet:

    Dim i As Long
'   Start at worksheet number 4
    For i = 4 To Worksheets.Count
        Sheets(i).Activate
        ' Your formatting code here
    Next i

Note. You can also reduce your code a bit. You do not need to actually use Select or Selection that much. Most lines of code that end with Select and the next line begins with Selection can be combined, i.e.
this:

[COLOR=#333333]Columns("M:N").Select[/COLOR]
[COLOR=#333333]Selection.ColumnWidth = 12.71[/COLOR]

can be combined to this:

[COLOR=#333333]Columns("M:N").[/COLOR][COLOR=#333333]ColumnWidth = 12.71[/COLOR]

as well sections like this:

[COLOR=#333333]Range("H1").Select[/COLOR]
[COLOR=#333333]ActiveCell.FormulaR1C1 = "=TODAY()+1"[/COLOR]

can be combined like this:

[COLOR=#333333]Range("H1").[/COLOR][COLOR=#333333]FormulaR1C1 = "=TODAY()+1"[/COLOR]

By getting rid of a lot of the Select and Activate statements, your code will be shorter and run more efficiently.

 

[Norie]

Try this.

Sub Macro1()
Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
    
        With ws
            .Columns("A:O").EntireColumn.AutoFit
            .Columns("M:N").ColumnWidth = 12.71
            .Rows("1:1").EntireRow.AutoFit
            .Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            
            With .Range("A1:O1")
                With .Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorLight2
                    .TintAndShade = 0.799981688894314
                    .PatternTintAndShade = 0
                End With

                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlBottom
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
            End With
            
            .Range("F1").Value = "Opportunity Report"
            .Range("H1").FormulaR1C1 = "=TODAY()+1"
            
            With .Range("H2:H40")
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
                                      Formula1:="=""Due"""
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
                With .FormatConditions(1).Font
                    .Color = -16383844
                    .TintAndShade = 0
                End With

                With .FormatConditions(1).Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = 13551615
                    .TintAndShade = 0
                End With
                .FormatConditions(1).StopIfTrue = False
            End With
        End With
        
    Next ws

End Sub

 

[RCBricker]

here is your code (cleaned up a little from the recording...excel is a bit over-zealous when it records).

It will now make your changes to each sheet and not multiple times to one sheet

Sub Macro1()
 '
 ' Macro1 Macro
 '
 ' Keyboard Shortcut: Ctrl+Shift+L
 '


 Dim ws As Worksheet
 For Each ws In ThisWorkbook.Worksheets
    ws.Select
     Columns("A:O").EntireColumn.AutoFit
     Columns("M:N").ColumnWidth = 12.71
     Rows("1:1").EntireRow.AutoFit
     Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
     
     With Range("A1:O1")
        With .Interior
           .ThemeColor = xlThemeColorLight2
           .TintAndShade = 0.799981688894314
        End With
        .HorizontalAlignment = xlCenter
     End With
     Range("F1").Value = "Opportunity Report"
     Range("H1").FormulaR1C1 = "=TODAY()+1"
     Range("H2:H40").Select
     Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
     Formula1:="=""Due"""
     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
     With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
     End With
 Next ws
    With Selection.FormatConditions(1).Interior
       .PatternColorIndex = xlAutomatic
       .Color = 13551615
       .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
 End Sub

 

[nayseem]

Thanks for the feedback - I will keep it in mind for future posts!

 

[nayseem]

I copied and pasted this into the VBA window and when I try to run it, it states "We couldn't do this for the selected range of cells. Select a single cell within a range of data and then try again."

So I highlighted the sheets and tried to run the macro again and it states this "Run-time error '1004'" - I have changed the text to red in which it is giving me the error for.

here is your code (cleaned up a little from the recording...excel is a bit over-zealous when it records).

It will now make your changes to each sheet and not multiple times to one sheet

Sub Macro1()
 '
 ' Macro1 Macro
 '
 ' Keyboard Shortcut: Ctrl+Shift+L
 '


 Dim ws As Worksheet
 For Each ws In ThisWorkbook.Worksheets
    ws.Select
     Columns("A:O").EntireColumn.AutoFit
     Columns("M:N").ColumnWidth = 12.71
     Rows("1:1").EntireRow.AutoFit
     Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
     
     With Range("A1:O1")
        With .Interior
           .ThemeColor = xlThemeColorLight2
           .TintAndShade = 0.799981688894314
        End With
        .HorizontalAlignment = xlCenter
     End With
     Range("F1").Value = "Opportunity Report"
     Range("H1").FormulaR1C1 = "=TODAY()+1"
     Range("H2:H40").Select
     Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
     Formula1:="=""Due"""
     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
     With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
     End With
 Next ws
    With Selection.FormatConditions(1).Interior
       .PatternColorIndex = xlAutomatic
       .Color = 13551615
       .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
 End Sub

 

[Norie]

You don't need to select anything, in fact having multiple sheets selected could cause problems like the one you describe.

 

[Joe4]

The ideal goal would be to have this macro format all macros except my first three worksheets as they are my summary, instructions and initial macro pages.

Did you see my reply? The loop I showed you allows you to exclude the first three sheets.

 

[nayseem]

I added it to the codes above but is actually pushing the rows down by three.