copy data from WB1 to different WS's in WB2

[picto]

Hi All

I need to transfer data from a daily workbook to an archive workbook but put the data onto different worksheets depending on the value in column 1.

eg
apple      data1      data2      data3
orange     data1      data2      data3
melon      data1      data2      data3
apple      data1      data2      data3

the data needs to go onto the next blank line on the worksheet named after each fruit. There are about 70 fruit to choose from.

Thanks
Shaun

 

[njimack]

Something like this. Change the workbook names as required...

Sub Copy()
Dim i As Long
Dim LR As Long
Dim sh As String
With Workbooks("Book1.xls").Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
 
    For i = 2 To LR
        sh = .Cells(i, 1)
        Range(.Cells(i, 1), .Cells(i, 4)).Copy Workbooks("Book2").Sheets(sh).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Next i
End With
End Sub