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.

 

[MARK858]

but not by trying to format words within code tags

The OP can format within code tags, they just need to use RICH rather than VBA tags.

 

[Micron]

I've seen code in rich tags and I'm not a fan of it. The vba tags do a nice job of formatting key words, which I find helps in the interpretation of code. If I see a blue "And" for example, I know it is being interpreted as a logical operator and not just a piece of text. That may not have been a great example, but hopefully I'm getting my point across with it. I like it better when OP id's an error raising line with a green comment line, especially if it's embellished a bit:

this Is a code line that does something '<< this is the comment that id's the problem line

Note the blue "Is"

 

[MARK858]

I have no problem with you having a personal preference, personally I find it harder to spot where the error line is if it is amongst other comments when using VBA tags.