Macro to insert new Excel row and paste certain named ranges into parts of that

Question

Macro to insert new Excel row and paste certain named ranges into parts of that row

I have an excel database that I use for case management. When a new record is added, a row is inserted in alphabetical order by client last name and some cells are filled using lookup formulae that are in named ranges. In another cell in the new row I would like to paste the value of a different named cell (but not a reference to that cell). This last is our file number and by inserting the file number for this file, the referenced cell is incremented.

What is below is a recorded macro which works to insert a row at a given point which was the selected row when the macro was recorded and then paste two sets of lookup formulae in that new row. I have added comments to the code.

Sub NewClientRow2()
'
' NewClientRow Macro
' Macro recorded 8/28/2014 by Charles K. Kenyon
'
'   Here I want to insert a new row above the selection point
    Selection.Insert Shift:=xlDown
'   Next is to copy the named range "Judge_Formulae"
    Range("Judge_Formulae").Copy
'   Next I want to go to the new row and paste the selection beginning with '     column Z
    Range("Z22").PasteSpecial (xlPasteFormulasAndNumberFormats)
'   Next I want to copy the named range "Clerk_Formulae"
    Range("Clerk_Formulae").Copy
'   Next I want to go to the new row and paste the clipboard beginning with '

'     column AQ
    Range("AQ22").PasteSpecial (xlPasteFormulasAndNumberFormats)
'   Next Paste the Next Number in for file number in column Q
    Dim lNext As Long
    lNext = Range("Next_Number").Value
    Range("Q22").Value = lNext
End Sub

Somehow, I need to reference the inserted row and use that instead of "22" in the above.

Explanation / Answer

.,.,Hi,vm

try this...^$4

Sub Selection_AddNewRowAbove()

'Aug 30, 2014

If MsgBox("select the desired row ?", vbOKCancel, "row" & Selection.Row) = vbCancel Then Exit Sub

Dim r As Long
r = Selection.Row
Selection.EntireRow.Insert
Range("Judge_Formulae").Copy
Cells(r, "Z").PasteSpecial (xlPasteFormulasAndNumberFormats)
Range("Clerk_Formulae").Copy
Cells(r, "AQ").PasteSpecial (xlPasteFormulasAndNumberFormats)
Application.CutCopyMode = False

Dim N as long

N=Range("Next_Number").Value

Cells(r, "Q").Value = N

End Sub

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.