Copy rows of data from one sheet to another

Closed
Lil MIS - May 23, 2011 at 10:46 AM
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 - May 24, 2011 at 03:56 AM
Hello,

I have used the below code to copy selected rows ("x") of data from one worksheet ("QBank") to another ("TestSheet") within the same workbook.

Background:
1. "QBank" worksheet has heading in row one so it will start checking the data in row 2
2. Column D does not have any empty cells.
3. "TestSheet" worksheet will have headings in the first 5 rows so the data will start copying in row 6.

Set i = Sheets("QBank")
Set e = Sheets("TestSheet")
Dim d
Dim j
d = 5
j = 2

Do Until IsEmpty(i.Range("D" & j))

If i.Range("D" & j) = "x" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value

End If
j = j + 1
Loop

"TestSheet" worksheet has other data in it which I do not want to overwrite from row 20 onwards, how can I insert the number of rows above this when copying the data from the "QBank" worksheet to preserve everything below if more than 15 rows are selected in the "QBank" worksheet?

Any help would be appreciated.

1 reply

RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
May 24, 2011 at 03:56 AM
use following line in your code as per your accordance. (you can either use if in your code or can delete 6 to 20 row in sheet "TestSheet")

with worksheet e
Range(("D" & j).EntireRow.Insert 
end with
0