Getting a Macro to Operate in Selected Range

Question

I created the perfect macro but of course as all macro's do it's standard - only working in the exact rows it was recorded in. I need it to work in any row I highlight and I've tried a variety of custom coding. I can't get it to do anything but the same formula and formatting on top of the same area. Always row 5. This is the code...

Sub OrschelnMacro()
'
' OrschelnMacro Macro
'
' Keyboard Shortcut: Ctrl+p
'
    Rows("5:5").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E5").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
    Range("G5").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
    Range("H5").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "1 of 1"
    Rows("5:5").Select
    Selection.RowHeight = 75
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .Name = "Calibri"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 26
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Selection.Font.Bold = True
    Range("H5").Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 72
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("E5:G5").Select
    With Selection
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("H5").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A5:H5").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("K7").Select
End Sub

Does anyone have any ideas? Thank you so much in advance...

Answer 1

Replace this:

Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E5").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("G5").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("H5").Select
ActiveCell.FormulaR1C1 = "1"
Range("F5").Select
ActiveCell.FormulaR1C1 = "1 of 1"
Rows("5:5").Select
Selection.RowHeight = 75

with

Dim myRow As Long    

myRow = Selection.Row

Rows(myRow).Insert
Range("E" & myRow & ":H" & myRow).FormulaR1C1 = _
    Array("=SUM(R[-2]C:R[-1]C)", "1 of 1", "=SUM(R[-2]C:R[-1]C)", "1")

Rows(myRow).RowHeight = 75

I won't get into all the formatting code as it's not really what your question is about - the point is that you can use a variable to get the .Row property and use that in your code.

Answer 2

I created a quick bit of your code to do what you are asking on any row, you just have to be clicked in any cell of the row you want it to run on. I wouldn't recommend this code, as it is rather sloppy and has a lot of duplicate code, but it works. I would work on learning what it is doing and get rid of any extra code.

I also got rid of almost all the select statements as they slow down your code, instead of selecting a cell and then setting the formula, you just put that in one line as I did with your code.

Good luck learning VBA, it's fun and you have plenty of knowledgeable people on this site for you to get assistance.

Sub OrschelnMacro()
'
' OrschelnMacro Macro
'
' Keyboard Shortcut: Ctrl+p
'
curRow = Selection.Row
Selection.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E" & curRow).FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("G" & curRow).FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("H" & curRow).FormulaR1C1 = "1"
Range("F" & curRow).FormulaR1C1 = "1 of 1"
Rows(curRow).RowHeight = 75
With Rows(curRow).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 65535
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
With Rows(curRow).Font
    .Name = "Calibri"
    .Size = 26
    .Bold = True
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .ThemeFont = xlThemeFontMinor
End With
Range("H" & curRow).Select
With Range("H" & curRow).Font
    .Name = "Calibri"
    .Size = 72
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .ThemeFont = xlThemeFontMinor
End With
With Range("H" & curRow)
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlTop
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
With Range("E" & curRow & ":G" & curRow)
    .VerticalAlignment = xlTop
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
With Range("H" & curRow)
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlTop
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
Range("A" & curRow & ":H" & curRow).Borders(xlDiagonalDown).LineStyle = xlNone
Range("A" & curRow & ":H" & curRow).Borders(xlDiagonalUp).LineStyle = xlNone
With Range("A" & curRow & ":H" & curRow).Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Range("A" & curRow & ":H" & curRow).Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Range("A" & curRow & ":H" & curRow).Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Range("A" & curRow & ":H" & curRow).Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
Range("A" & curRow & ":H" & curRow).Borders(xlInsideVertical).LineStyle = xlNone
Range("A" & curRow & ":H" & curRow).Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub