I imported 20,000+ rows and 150+ columns from Filemaker Pro into Excel
and I would like to re-format it before transferring into Access.
Right now, all the data are into 1 worksheet but I would like to be
able to separate them by category into multiple worksheets. Because
each record type doesnt need all 150+ columns or fields and I would
like to weed them out separately.

let's say I have this column with unique values:

RECORD TYPE
apple
orange
banana
strawberries

And each value appears in multiple rows.

I would like all the record type "apple" to be copied onto the
worksheet labeled "apple" and "orange" type records into "orange"
worksheet and so on. Additionally, few columns contains more than 255
characters -- is there a way to avoid truncating the data?

I tried "advance filter" and I keep getting an error message "can only
be copied onto active worksheet" (or something like that).

Any help or guidance is truly appreciated.
Thank you,

Sharon

I have this code but I couldn't figure out how to move on to the next
blank row in wsNAME instead of copying it over A2:A2.

-----------
Sub CopyRows()

Dim x As Long
Dim lRow As Long
Dim recType As String
Dim newRange As Range
Dim wsName As Worksheet
Dim acName As Worksheet

Set acName = Worksheets("Orig")

x = 2
y = 2
lRow = InputBox("Enter Last Row Number")



For oRow = 2 To lRow

recType = acName.Cells(y, 1)
Select Case recType
Case "Investigation Div"
Set wsName = Worksheets("Investigation Div")
Case "Anonymous Tip"
Set wsName = Worksheets("Anonymous Tip")
Case "DE 2660"
Set wsName = Worksheets("DE 2660")
Case "Pattern Claims"
Set wsName = Worksheets("Pattern Claims")
Case "Staff Referral"
Set wsName = Worksheets("Staff Referral")
Case Else
Set wsName = Worksheets("Blank")
End Select

Set newRange = wsName.Range("A2:A2") 'I'm stuck here

Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Copy

newRange.PasteSpecial
Set newRange = newRange.Offset(1, 0)
'I know the last line is useless since next oRow will initialize
newRange back to A2:A2

x = x + 1
y = y + 1

Next oRow

End Sub


---------
Can someone please help me with the above code.
Thank you so much.

It appears that you only have 6 different record types ("Investigation
Div","Anonymous Tip, "Pattern Claims","Staff Referral", and all others
("Blank")).

I would still suggest my original idea of simply doing 6 separate exports
from Filemaker Pro, then import those data extracts into Access. You can
export only the fields that you need for each record type. As you
mentioned, you only have about 500 rows of data for each record type.

You will avoid accidentally corrupting your data by NOT using Excel.
(Clicking on a column heading in Excel, then sorting will sort only that
column, corrupting the data, for example! Believe me, I have accidentally
done this myself!)

--
Regards,
Bill Renaud