PDA

View Full Version : copy value and insert new row then paste



anilg0001
06-17-2013, 10:31 PM
if my Q&i range has value then i need to copy Q&i to T&i value and insert a blank row below the q&i row then paste the copied value to newly inserted row M&i

i wrote this but shows error
any one help

Sub main1()
Dim i As Integer
For i = 3 To 15
Range("Q" & i).Select
If Range("Q" & i).Value <> "" Then
'ActiveCell.Offset(0, 4).Copy
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.Offset(0, 4).Copy
Range("M" & i + 1).Select
active cell.value=""
'Range.Resize(1, 4).Copy 'Destination:=Worksheets("Master File").Range("M" & .Row + 1)
End If
Next i
i = i + 1
End Sub

mancubus
06-17-2013, 11:07 PM
what i understand is if Q&i is not blank, insert a blank row after i (which is i + 1) and copy T&i to M&i+1


Sub main1()
Dim i As Integer, LR As Long
LR = Range("Q" & Rows.Count).End(xlUp).Row
For i = LR To 3 Step -1
If Range("Q" & i).Value <> "" Then
Rows(i + 1).EntireRow.Insert
Range("T" & i).Copy Range("M" & i + 1)
End If
Next i
End Sub

patel
06-17-2013, 11:09 PM
it's not clear for me what you want but try this code
Sub main2()
Dim i As Integer
For i = 15 To 3 Step -1
Range("Q" & i).Select
If Range("Q" & i).Value <> "" Then
'ActiveCell.Offset(0, 4).Copy
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.Offset(0, 4).Copy Range("M" & i + 1)
'Range.Resize(1, 4).Copy 'Destination:=Worksheets("Master File").Range("M" & .Row + 1)
End If
Next i
End Sub