-
Solved: Insert a specific row at the ActiveCell
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
-
try this:
[VBA]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[/VBA]
-
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.....
-
then try this:
[VBA]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[/VBA]
-
another success!
brilliant.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules