Run a macro on a specific worksheet

[dclrvtt01]

I used the recorder to create a macro on 'Sheet2' and it works flawlessly. Then I created a command button on 'Sheet 1' to run the macro on 'Sheet 2'.

Unfortunately, when I click the command button, it only runs on 'Sheet 1'. I tried using the select sheets("Sheet2").activate code, but it wasn't working. I've listed the code below. Can you please help? Thanks in advance.

Sub MMkt_Analysis_Tab()
'
' MMkt_Analysis_Tab Macro
'
'
Rows("24:42").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A6:M23").Select
Selection.Copy
Range("A25").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Selection.Font.Underline = xlUnderlineStyleDouble
Selection.Font.Underline = xlUnderlineStyleNone
Rows("25:27").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = True
Range("A27:M27").Select
Selection.Font.Underline = xlUnderlineStyleSingle
Selection.Font.Underline = xlUnderlineStyleNone
Range("A27,C27,E27,G27,I27,K27,M27").Select
Range("M27").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("C40,E40,G40,I40,K40,M40").Select
Range("M40").Activate
Selection.Font.Underline = xlUnderlineStyleSingleAccounting
Range("C42,E42,G42,I42,K42,M42").Select
Range("M42").Activate
Selection.Font.Underline = xlUnderlineStyleDoubleAccounting
End Sub

 

[VoG]

Try like this

Sub MMkt_Analysis_Tab()
'
' MMkt_Analysis_Tab Macro
'
'
With Sheets("Sheet2")
    .Rows("24:42").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("A6:M23").Copy
    .Range("A25").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    .Range("A25").Font.Underline = xlUnderlineStyleDouble
    .Range("A25").Font.Underline = xlUnderlineStyleNone
    With .Rows("25:27")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    .Rows("25:27").Font.Bold = True
    With .Range("A27:M27")
        .Font.Underline = xlUnderlineStyleSingle
        .Font.Underline = xlUnderlineStyleNone
    End With
    With .Range("M27")
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlEdgeLeft).LineStyle = xlNone
        .Borders(xlEdgeTop).LineStyle = xlNone
        With .Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        .Borders(xlEdgeRight).LineStyle = xlNone
        .Borders(xlInsideVertical).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
    With .Range("C40,E40,G40,I40,K40,M40")
        .Font.Underline = xlUnderlineStyleSingleAccounting
    End With
    With .Range("C42,E42,G42,I42,K42,M42")
        .Font.Underline = xlUnderlineStyleDoubleAccounting
    End With
End With
End Sub

 

[dclrvtt01]

Thanks for the quick reply. Unfortunately, when I copied the code, I got a Compile error. Syntax error. Any thoughts?

 

[VoG]

That code compiles fine for me. Where is the syntax error?

 

[dclrvtt01]

Excuse my ignorance since I'm new to VBA, but if it is the line highlighted in yellow, then it is the first line Sub MMkt_Analysis_Tab(). What do you think?

 

[VoG]

You need to replace your original code with the code that I posted.

 

[dclrvtt01]

Thanks for your help. Sorry it took me so long to get back to you. It is working except for one issue. When I run the macro through a control button, it adds a double underline in each copied cell. However, if I hit the play button in VBA, it doesn't add the double underline. Do you know what would be causing that? Thanks again.

 

[dclrvtt01]

To clarify things, it only adds the double underline if the control button is on a different worksheet within the workbook. I added one to the selected worksheet and it works fine.