How to tell a macro what sheet to run on?

[nbraybrook]

I made some macros that ran fine, until they wanted all the macros to be placed on a specific worksheet. I have all my macros now on one worksheet, but the macros do not work now I assume because they are not located on the worksheet they ran on anymore? What code do I add to my macros to tell them to run on sheet1 even if they are on sheet2? Or just code that will let me tell it what worksheet to run on? Thanks in advance!

Sub ENGREV()
'
' ENGREV Macro
'

'
    Rows("5:5").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C5:U5").Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("C6:U6").Select
    Range("U6").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("C5").Select
End Sub

 

[VoG]

Who are "they"?

The code should go on a regular module, then it will operate on whichever sheet is selected.

 

[nbraybrook]

Sorry "they" are my bosses. They want all the macros tied to one sheet so they can insert this sheet into older workbooks and "retrofit" older workbooks with all the new macro functions. Problem I am having is macrons run fine on the sheet they are on but i cant get them to run on other sheets in the workbook. Its like the macro doesnt know to go to that sheet. Sorry im an excel noobie.

 

[VoG]

Well "they" are mental. see if this works

Sub ENGREV()
'
' ENGREV Macro
'

'
With ActiveSheet
    .Rows("5:5").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    'Range("C5:U5").Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    .Range("C6:U6").Select
    .Range("U6").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    .Range("C5").Select
End If
End Sub

 

[nbraybrook]

I am getting an error "Compile error: End If without block If"

 

[davesfx]

Remove the "End If" (2nd to last line of code)

You show an End if, but no if statement to end.

Hope this helps out.