Copy rows of data from one sheet to another [Closed]

Report
-
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
-
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

Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
118
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!