Finding and copying from data file

jpen

Active Member
Joined
Jun 19, 2002
Messages
393
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
ABCD
11a1355.50%
2a2181.50%
32b1538.70%
4b2296.20%
5b3584.90%
6b4378.70%
7b5270.50%
83c1687.30%
9c2687.90%
10c3386.80%
11----------
12AverageQQQQ3.469.60%
13
141a1191.10%
15----------
16AverageXXXX191.10%
17
181a1550.00%
19a20.0!
20a3590.00%
21a4333.30%
222b1383.30%
23b2383.30%
243c1560.00%
25c2641.70%
26c3633.30%
274d15100.00%
28d2366.70%
29----------
30AverageYYYY3.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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
hi -
try this one.
data in first sheet, other sheets QQQQ,XXXX,YYYY to the right

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,789
Messages
5,544,290
Members
410,601
Latest member
Silver2
Top