Finding and copying from data file

[jpen]

Hi all,

I want to copy data from a data file to another file.

First I have to find the name in the data file (QQQQ, XXXX, YYYY) and then copy the data above the name. See example.

CLIPBORD.xls
	A	B	C	D
1	1	a1	3	55.50%
2		a2	1	81.50%
3	2	b1	5	38.70%
4		b2	2	96.20%
5		b3	5	84.90%
6		b4	3	78.70%
7		b5	2	70.50%
8	3	c1	6	87.30%
9		c2	6	87.90%
10		c3	3	86.80%
11			-----	-----
12	Average	QQQQ	3.4	69.60%
13
14	1	a1	1	91.10%
15			-----	-----
16	Average	XXXX	1	91.10%
17
18	1	a1	5	50.00%
19		a2	0.0!
20		a3	5	90.00%
21		a4	3	33.30%
22	2	b1	3	83.30%
23		b2	3	83.30%
24	3	c1	5	60.00%
25		c2	6	41.70%
26		c3	6	33.30%
27	4	d1	5	100.00%
28		d2	3	66.70%
29			-----	-----
30	Average	YYYY	3.8!	69.20%
CLIPBORD

For QQQQ there are 10 rows of data.
For XXXX there are 1 rows of data.
For YYYY there are 11 rows of data.

These data I want to copy to the other file where I have worksheets called QQQQ, XXXX and YYYY.

Has anyone a idea how to solve this problem.


JH

 

[lozzablake]

Are there always 10,1 and 11 rows of data or can the number of rows vary?

 

[jpen]

No the number of rows vary.

JH

 

[lozzablake]

Is there only one instance of each type. ie just one QQQQ, XXXX and YYYY?

 

[agihcam]

hi -
try this one.
data in first sheet, other sheets QQQQ,XXXX,YYYY to the right

Sub sample()
On Error Resume Next
Dim a
Dim i, ii As Long
ii = Sheets(1).Range("b" & Rows.Count).End(xlUp).Row
For i = 2 To Sheets.Count
    With Sheets(1).Range("b1:b" & ii)
        Set c = .Find(Sheets(i).Name, , , xlWhole)
            If Not c Is Nothing Then
                a = c.Offset(, 1).End(xlUp).Row
                    Range("a" & a & ":d" & c.Row).Copy Sheets(i).Range("a" & Rows.Count).End(xlUp).Offset(1)
            End If
    End With
Next
End Sub