Copy certain rows from one worksheet to another

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

azu_daioh@yahoo.com - 12 Dec 2007 00:09 GMT

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

Reply to this Message

Bill Renaud - 12 Dec 2007 18:19 GMT

If you only have a half dozen or so different record types (apple, orange,
etc.), it might just be easier to perform separate data exports from
Filemaker Pro with only the fields that are needed for that record type,
then import each table directly to Access. Skip using Excel, unless you
have other cleanup operations that are easiest to do in Excel.

(Just my 2 cents worth.)

If you really want to do this in Excel, you could try building a pivot
table of all of the data. (Try this with a small set of dummy data first,
to verify the concept.)

1. Set RECORD TYPE as a Page field.
2. Right click on the Page field (RECORD TYPE), and choose "Show Pages...".
This will expand the pivot table out to separate pivot tables on separate
pages, one for each RECORD TYPE (apple, orange, etc.).
3. Go to each new pivot table on its own page and double click on the Grand
Total cell at the lower right corner of the pivot table. This will now
"expand" the pivot table out to a new separate page with all of the data
for that (sub) pivot table.

Signature

Regards,
Bill Renaud

Reply to this Message

azu_daioh@yahoo.com - 12 Dec 2007 22:30 GMT

On Dec 12, 10:19 am, "Bill Renaud" <Bill.No.Spam.Ren...@Comcast.Net>
wrote:

> If you only have a half dozen or so different record types (apple, orange,
> etc.), it might just be easier to perform separate data exports from
[quoted text clipped - 20 lines]
> Regards,
> Bill Renaud

Thanks Bill but unfortunately, I have over 500 rows for each record
type. But I will try using pivot table. It might work. Thank you.

Reply to this Message

azu_daioh@yahoo.com - 13 Dec 2007 01:01 GMT

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.

Reply to this Message

Bill Renaud - 13 Dec 2007 17:10 GMT

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!)

Signature

Regards,
Bill Renaud

Reply to this Message