This line caused an error:
Set ws = wbDest.Sheets(CStr(Trim(Range("A" & lngRow))))

After testing, I realised it's because it won't find a destination sheet for
all the values in column A.
I've put in "on error resume next". That works (unbelieveably quickly!) but
will it be enough and not create problems later ?

--
WinXP - Office2003 (Italian)


"David Macdonald" wrote:

OK I set up 2 new workbooks and your code works a dream!
Now I just have to try it out on my originals...

--
WinXP - Office2003 (Italian)


"Jacob Skaria" wrote:

Hi David

Suppose your data looks like

Col A Col B
Header 1 Header 2
111 1
111 2
111 3
222 1
222 2
222 3
333 1
333 2
333 3
333 4

the below macro will do what you are looking for..

Points to be noted
--Activesheet is the data sheet.
--Change the destination workbook name ("Book3"). If this is a saved
workbook it will need to have the extension .xls/.xlsx
--Try the below and feedback


Sub Macro()
Dim lngRow As Long, lngNextRow As Long
Dim wbDest As Workbook, ws As Worksheet
Set wbDest = Workbooks("Book3")

For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row
If Trim(Range("A" & lngRow)) < "" Then
Set ws = wbDest.Sheets(CStr(Trim(Range("A" & lngRow))))
lngNextRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(lngRow).Copy ws.Rows(lngNextRow)
Set ws = Nothing
End If
Next
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"David Macdonald" wrote:

I have a database in one WorkBook and want to transfer filtered data on
setarate sheets in another WorkBook.
One of the columns in the db is a 5 digit number and the sheets in the
destination Workbook are named with the same numbers.
I want to get all the rows containing the sheet name onto the correct sheets.
i.e. All the rows in WB1 referencing 12345 should be transfered to WB2 sheet
12345, all the rows in WB1 referncing 12346 should be transfered to WB2 sheet
12346, you get the idea...
I could just have Excel switch back and forth between the 2 workbooks,
filter, copy and paste OR (I thought) I could place the whole database in an
array, then filter for the results I need as I step through the different
sheets. Now I have my 3000 row x 16 column array BUT I can't figure how to
filter an array...
Should I just give up and let my users get dizzy watching Excel flick
between 2 workbooks for a couple of minutes ?

--
WinXP - Office2003 (Italian)