VBA Macro to run on all but one sheet?

[MrRob2020]

Hi Everybody,

I have an issue which seems to have already solved by a google search, however due to my considerable lack of knowledge, my code is not.

I have made a macro using the record function, this macro changes number of different things; date formats, column widths, formula etc.

It works perfectly on the sheet I am on.

However, I would like the macro to produce the same results on all sheets, bar one.

Below is the code from the recorded macro:

Sub Format_Sheets()

Rows("1:2").Select

Application.CutCopyMode = False

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorDark1

.TintAndShade = 0

.PatternTintAndShade = 0

End With

Columns("C:C").Select

Selection.NumberFormat = "m/d/yyyy"

Range("K4").Select

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"

Range("K4").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.FillDown

Columns("A:K").Select

Columns("A:K").EntireColumn.AutoFit

Range("B1:C2").Select

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Selection.Merge

ActiveCell.FormulaR1C1 = "RECONCILIATION SHEET"

Range("B1:B2").Select

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = True

End With

With Selection

.HorizontalAlignment = xlLeft

.VerticalAlignment = xlCenter

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = True

End With

With Selection.Font

.ThemeColor = xlThemeColorAccent4

.TintAndShade = -0.249977111117893

End With

Selection.Font.Bold = True



End Sub

While searching for a solution, I found some additional code to wrap around my current.

I understand the theory, however I think my inability to code is a problem here.
From what I can understand, I may be struggling with, at least, defining ranges?

It seems it was not as easy as just inserting my recorded macro code into the googled solution.

Sub Format_All_Macros()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

If ws.Name <> "HEADER SHEET" Then



Rows("1:2").Select

Application.CutCopyMode = False

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorDark1

.TintAndShade = 0

.PatternTintAndShade = 0

End With

Columns("C:C").Select

Selection.NumberFormat = "m/d/yyyy"

Range("K4").Select

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"

Range("K4").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.FillDown

Columns("A:K").Select

Columns("A:K").EntireColumn.AutoFit

Range("B1:C2").Select

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Selection.Merge

ActiveCell.FormulaR1C1 = "RECONCILIATION SHEET"

Range("B1:B2").Select

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = True

End With

With Selection

.HorizontalAlignment = xlLeft

.VerticalAlignment = xlCenter

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = True

End With

With Selection.Font

.ThemeColor = xlThemeColorAccent4

.TintAndShade = -0.249977111117893

End With

Selection.Font.Bold = True

End If
Next
End Sub

I hope this is an easy job?

 

[Fluff]

The simplest option is to add this line as shown

If ws.Name <> "HEADER SHEET" Then

ws.Activate

Rows("1:2").Select

 

[MrRob2020]

The simplest option is to add this line as shown

If ws.Name <> "HEADER SHEET" Then

ws.Activate

Rows("1:2").Select

Amazing, thank you!

 

[Fluff]

Glad to help & thanks for the feedback.