I am using Excel 2010 on a Win7 machine.
I have a macro that will prompt the user to select a cell.
Then the macro will select that entire row.
Then another prompt will ask the user how many rows they want copied.
Then it will copy that row selected and insert how ever many rows the user prompted on No. 3 above.
What I need:
A way to instantly change the pasted values in one cell/coloumn of each newly pasted row..
I can't seem to figure out how to select the rows I inserted (based on user input) to change the value automatically.. since the active cell will change often, I can't do it based on a range.. it has to be whatever rows were inserted by the user prompt..
i.e.
1.1.1.1 I0127955 29.4 m
10.1.1.1 TAG-01 12 m
User selects I0127955 and then the row is copied via macro. And then new rows are inserted via user input (in example below userinput = 2) and then the data becomes this with macro:
1.1.1.1 I0127955 29.4 m
1.1.1.1 I0127955 29.4 m
1.1.1.1 I0127955 29.4 m
10.1.1.1 TAG-01 12 m
I need it to become this:
1.1.1.1 I0127955 29.4 m
new I0127955 29.4 m
new I0127955 29.4 m
10.1.1.1 TAG-01 12 m
Here is my new and improved code!
Sub MasterTagSelect()
Dim rRange As Range
Dim lngRows As Long
On Error Resume Next
Application.DisplayAlerts = False
Application.ScreenUpdating = True
Set rRange = Application.InputBox(prompt:= _
"Please select a Master Tag to Split.", _
Title:="SPECIFY MASTER TAG", Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True
If rRange Is Nothing Then
Exit Sub
Else
rRange.Select
ActiveCell.EntireRow.Select
userInput = Application.InputBox("How many Detail Tags do you wish to insert? (Must be at least 2)", "Insert Rows", , , , , , 1)
t = Int(Val(userInput))
If userInput = False Then Exit Sub
With Selection
.Copy
ActiveCell.Offset(1).Resize(userInput).EntireRow.Insert
End With
Application.CutCopyMode = False
Finish:
If Err.Number <> 0 Then MsgBox prompt:="Please ensure you only enter numeric values!"
End If
End Sub
I can't figure out how to define the selection without a range..
Thanks for any help, everyone
Bookmarks