Macro Formatting to all WS

[smk13]

Hi Excel Masters,

I have recorded a macro for cell validation update, CF, and general cell formatting, that I'm applying to a large number of worksheets within a workbook. Right now, I have a macro set up that helps automate a lot of this process, but I'm wondering if there's a way to apply this macro via VBA to all the worksheets? In the recording it obviously captured the WS that I selected in the code. I went and changed it from the WS name to 'ActiveSheet' but errors. See the code below.

Thanks for the help!

Sub Macro3()
Dim xsheet As Worksheet
For Each xsheet In ThisWorkbook.Worksheets
xsheet.Select

'
' cellvalidation_CF_formatting Macro
'
' Keyboard shortcut: Ctrl+w
'
Columns("I:I").Select
Range("I5").Activate
Selection.Copy
Selection.Insert Shift:=xlToRight
Range("I7").Select
Sheets("J2.0 SubSurface-A12").Select
Rows("6:6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("ActiveSheet").Select
Rows("6:22").Select
Range("D6").Activate
Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("I6").Select
Application.CutCopyMode = False
Range("I6").Select
ActiveCell.FormulaR1C1 = ""
Range("I8").Select
Sheets("J2.0 SubSurface-A12").Select
Range("I5").Select
Selection.Copy
Sheets("ActiveSheet").Select
Range("I5").Select
ActiveSheet.Paste
Columns("I:I").Select
Range("I5").Activate
Selection.FormatConditions.Delete
Range("I6").Select

Next xsheet

End Sub

 

[Micron]

If I may suggest:
- post your code within code tags (use vba button on posting toolbar) to maintain indentation and enhance readability
- always post the error message (error number alone is seldom helpful)
- always ID the line that raises the error, but not by trying to format words within code tags

I tried your code on a new wb with 4 empty sheets and got no errors as long as I used my own sheet names of course. Not sure if the lack of data is the reason for that.