Insertline Macro

[Guest]

I'm trying to write a simple macro in VB to insert a line into a spreadsheet
that I have been developing. The spreadsheet is a for Project Managers to
note snagging items in refurbishment projects. The sheet has a number of
formulas on each row. I would like to create a command button that allows the
user to select a row and insert a line at the activecell (copying and pasting
a clear line that I have hidden at the bottom of the sheet).

My problem is that I don't know how to prompt/allow the user to select a
line. With the current code (a simplified version below) the macro inserts
the line below the line I am copying (because its the activecell).

Rows("9:9").Select
Selection.Copy
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown

Could anyone tell me the code that allows me to break the macro, allow the
user to select a cell/row and the restart the macro.

Any help would be much appreciated.

Phil

 

[Ed]

Unfortunately, you can't "break into" macro for user interaction. The
method I use when I have to allow the user to interact is to create two
macros: the first macro runs until user interaction is required, then pops
up a modeless UserForm with a button to continue the process. This allows
the user to do whatever, then click the button to run the second macro,
which completes the whole thing.

Ed

 

[Guest]

Hi
If you write
rows("9:9").copy
instead of selecting range then your activecell for pasting will be where
ever you start the macro from
or you could use this
Application.Goto Reference:=InputBox("PLEASE TYPE R PLUS ROW NUMBER REQUIRED
EG R6", Title:="WHAT")
After selection.copy
Hope this helps
Tina
Tina

 

[Tom Ogilvy]

Sub InsertRow()
Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox("Select location to insert row; use mouse to
select", Type:=8)
On Error GoTo 0
If Not rng Is Nothing Then
Rows(9).Copy
rng.EntireRow.Insert xlShiftDown
End If