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

 

[nayseem]

I un-selected everything and I'm receiving the same "Run-time error '1004'"

I'm not sure what I'm doing incorrectly.

 

[Norie]

Did you try the code I posted?

Are any of the sheets in the workbook protected or have merged cells?

 

[Joe4]

There have been various replies and it sounds like you have made various adjustments. So, if you are still having issues, it might be best to re-post your code, as you have it now.

 

[nayseem]

This is the code that I have in there right now. I also substituted the Dim ws As Worksheet with the code Joe4 posted above.

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

 

[Norie]

Where does that code break down?

Also, as I asked previously, are any sheets protected or do any have merged cells on them?

 

[nayseem]

None of the sheets are protected or have merged cells.

As soon as I hit run macro it pops up with the run-time error '1004'.

Also it highlights the following portion of the code:

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

 

[Norie]

Any chart sheets?

Any shapes on any of the sheets?

Does it make a difference if we ignore the first 3 sheets, which I kind of missed in the original post.

Sub Macro1()
Dim ws As Worksheet
Dim I As Long

    For I = 4 To ThisWorkbook.Sheets.Count
        
        Set ws = ThisWorkbook.Sheets(I)

        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 I

End Sub

 

[nayseem]

That code worked!

Thank you all for helping me out - this will cut down my time by half.