PDA

View Full Version : Solved: Insert a specific row at the ActiveCell



sunilmulay
10-06-2008, 03:18 AM
I'm a complete VBA newbie.
I've got a worksheet where I want to create an "insert row" macro which copies rows 101:106 and pastes it above the active cell that the user selects. Using the below code, the macro pastes the rows below row 106, and not at the row the user selects....

What's the best way to fix this?
Sub InsertRowPlanning()
'
' InsertRowPlanning Macro
'
ActiveSheet.Unprotect Password:=PWORD
Rows("101:106").Select
Selection.Copy
ActiveCell.Select
Selection.Insert Shift:=xlDown
ActiveCell.EntireRow.Select
Application.CutCopyMode = False
ActiveSheet.Protect Password:=PWORD
End Sub
Thanks in advance!
Sunil

MaximS
10-06-2008, 03:59 AM
try this:

Sub CopyWhereSelected()
ActiveSheet.Unprotect Password:=PWORD
Dim SelectedRow As Long

SelectedRow = ActiveCell.Row

Rows("1:5").Copy Destination:=Rows(SelectedRow - 1)
Application.CutCopyMode = False
ActiveSheet.Protect Password:=PWORD
End Sub

sunilmulay
10-06-2008, 04:26 AM
I think the code is almost there for this one. Only problem is, the macro is pasting over the existing rows....I want it to INSERT them in, like when using Copy and Insert Copied Rows command in Excel....
Thanks
S
ps. You have no idea how much of a relief it is for me to have someone helping out.....

MaximS
10-06-2008, 04:40 AM
then try this:

Sub CopyWhereSelected()

ActiveSheet.Unprotect Password:=PWORD

Dim SelectedRow As Long

SelectedRow = ActiveCell.Row

Rows("105:106").Copy
Range("A" & SelectedRow).Insert Shift:=xlDown
Application.CutCopyMode = False

ActiveSheet.Protect Password:=PWORD
End Sub

sunilmulay
10-06-2008, 04:57 AM
another success!
brilliant.