Run macro from selected cell location

[Shad2U]

Hello,

How do I edit the macro below to run from a selected cell location? I would like to be able to run the macro in other rows when I choose, and I'm unsure how to do this. Thank you.

Sub Add_5_Sub_Items()
'
' Add_5_Sub_Items Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Range("A29").Select
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Rows("28:28").Select
Selection.Copy
Rows("29:33").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A29:AO33").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
ActiveWindow.LargeScroll ToRight:=-2
Range("A29").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+0.1"
Range("A29").Select
Selection.AutoFill Destination:=Range("A29:A33"), Type:=xlFillDefault
Range("A29:A33").Select
Range("A29:C33").Select
Selection.InsertIndent 1
Rows("29:33").Select
Selection.Font.Italic = True
ActiveWindow.SmallScroll ToRight:=12
Range("AB28").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[1]C:R[5]C)"
Range("AB28").Select
Selection.AutoFill Destination:=Range("AB28:AH28"), Type:=xlFillDefault
Range("AB28:AH28").Select
Range("AH28").Select
Selection.Copy
ActiveWindow.SmallScroll ToRight:=3
Range("AK28").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("AL28").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=1
ActiveWindow.LargeScroll ToRight:=-2
End Sub

 

[Micron]

That means any row in a particular column? Perhaps in the worksheet selectionChange event between Range("A29").Select and your End Sub:

If Not Intersect(Target, Range("J:J")) Is Nothing Then

Change J:J to the column you need.
Please post code between code tags (use vba button on posting toolbar) and maintain proper indentation. It's so much easier to follow that way.
Alternatively, you could use the Target test and just call your current sub rather than move that code to the SelectionChange event.

 

[Shad2U]

That means any row in a particular column? Perhaps in the worksheet selectionChange event between Range("A29").Select and your End Sub:

If Not Intersect(Target, Range("J:J")) Is Nothing Then

Change J:J to the column you need.
Please post code between code tags (use vba button on posting toolbar) and maintain proper indentation. It's so much easier to follow that way.
Alternatively, you could use the Target test and just call your current sub rather than move that code to the SelectionChange event.

Thank you Micron for the reply. I apologize for the formatting. I've re-posted the code using the VBA button as instructed.

I'm sorry, I don't follow your response. I would be selecting a cell in column A, and then running the macro. I am using a button in the ribbon to run the macro. In my spreadsheet, I would input data into rows 28 through 100. As the macro is written now, If I wanted to use row 28 as a parent item and add subitems to it, I would select A29, and run the macro. It works great, but I'm limited to only performing this for row 28. I don't know how to write the macro to allow me to run the same macro on say row 46.

Sub Add_5_Sub_Items()
'
' Add_5_Sub_Items Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
    Range("A29").Select
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Rows("28:28").Select
    Selection.Copy
    Rows("29:33").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A29:AO33").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
    ActiveWindow.LargeScroll ToRight:=-2
    Range("A29").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+0.1"
    Range("A29").Select
    Selection.AutoFill Destination:=Range("A29:A33"), Type:=xlFillDefault
    Range("A29:A33").Select
    Range("A29:C33").Select
    Selection.InsertIndent 1
    Rows("29:33").Select
    Selection.Font.Italic = True
    ActiveWindow.SmallScroll ToRight:=12
    Range("AB28").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[1]C:R[5]C)"
    Range("AB28").Select
    Selection.AutoFill Destination:=Range("AB28:AH28"), Type:=xlFillDefault
    Range("AB28:AH28").Select
    Range("AH28").Select
    Selection.Copy
    ActiveWindow.SmallScroll ToRight:=3
    Range("AK28").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("AL28").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.SmallScroll ToRight:=1
    ActiveWindow.LargeScroll ToRight:=-2
End Sub

 

[Micron]

As I see it, the problem with the button is how to know what cell or row was selected before the button click, because once the button is clicked, no cell is active AFAIK. If that's true (I'm an Excel vba novice so not sure) then you'd need a sheet module level variable that gets the address value every time someone clicks on a cell somewhere. Could do that with Worksheet_SelectionChange event unless you're already using that for something else. Then use the variable value in your code instead of "A29" for example. To deal with the other hard coded ranges you could use Offset(row number, column number).

I suppose you could handle this several different ways, one of which might be to use some other event if you don't like what I'm suggesting for your situation.