Can i move multiple lines between workbooks

[Guest]

I have a .csv format file listing speed cameras, and the different speeds are
identified by two characters at the end of each line - 30, 40, 50 etc.

I would like to create separate files for each speed, but Excel won't allow
me to cut/copy several lines to created a new file/workbook. Is there any way
round this?

Martin Ellis

 

[Stan Brown]

Sun, 16 Sep 2007 03:28:00 -0700 from Martin Ellis

I have a .csv format file listing speed cameras, and the different speeds are
identified by two characters at the end of each line - 30, 40, 50 etc.

I would like to create separate files for each speed, but Excel won't allow
me to cut/copy several lines to created a new file/workbook. Is there any way
round this?

Highlight the lines, select Copy. (You might need to highlight the
rectangular block of cells, rather than a group of lines.)

Create a new blank workbook.

Click into the cell where you want the upper left corner of the data.

Select Paste.

 

[Guest]

This is is what I've been doing, but when I choose 'Copy' a box appears which
says 'This command cannot be used on multiple selections'

Using copy (or cut)/paste on the lines individually is out of the question
as there are over 5000 lines in each file, and four files to process.

I need to be able to select a group of three, then a group of four further
down, then a group of six etc etc...

I'm familiar with selecting multiple items by holding down the Ctrl key, but
Excel (2003 XP) doesn't seem to want to let me do it.


Martin Ellis

 

[Gord Dibben]

Martin

Try autofiltering for each number then copy the results to the new workbook.

Data>Filter>Autofilter. When filtered, F5>Special>Visble cells only>OK

Copy those rows to your sheet in new book.

Or without filtering, just use CTRL + click on entire rows and copy those.

Excel will not allow single non-contiguous ranges to be selected and copied but
will allow non-contiguous rows or columns.


Gord Dibben MS Excel MVP

 

[Guest]

Sorry to sound thick Gord, but this is a new Excel user forum and I don't
really understand what you mean.

You refer to rows, but it's entire lines I need to copy.

An example line is:
(A) -2.23277 (B) 53.48145 (C) Redlight:1419-30
(A) -3.25674 (B) 47.17895 (C) Redlight:2786-60

It's the -30 (or 60) at the end which I want to sort by, but surely if I use
Auto Filter, I'm still not going to be able to choose the entire line which
contains the filtered information...or am I missing the point?

Martin Ellis

 

[George Gee]

Martin

Click on the Auto Filter down arrow in the column that contains the data
that you wish to filter on, and choose 'Custom'.
In the dialog box on the left, click the down arrow, and choose "Ends with".
type in the box on the right-hand side -30 or whatever, click OK.

George Gee

 

[Gord Dibben]

Excel sheets consist of cells, rows and columns.

Rows run across the sheet from left to right and Columns run vertically from top
to bottom.

You are trying to select cells which are just parts of a row.

To select entire rows, hold CTRL then click on the row headers at left
side.................the 1,2,3,4,5 etc.

In this manner you can select 1,3,7,9,31 etc.

You would be better off using the autofilter to get the rows you want.

Use a custom filter on column C with "contains" "-30" to get all rows with -30
to be visible. The others will be hidden.

Also, since you say you don't know what a row is, perhaps a basic tutorial would
assist.

Here are a few basic tutorials for Excel to get a beginner going.

http://www.usd.edu/trio/tut/excel/index.html

http://www.baycongroup.com/el0.htm

http://office.microsoft.com/en-us/training/CR061831141033.aspx


Gord

 

[Gord Dibben]

I like "ends with" better than my "contains"


Gord

 

[Guest]

George and Gord,

OK...so far so good. Than you for your patience. I'm familiar with using
Crtl to select non contiguous rows, but I've successfully isolated the rows I
want to transfer to a new sheet, using the filter, but I still don't see how
this helps me as when I try and select them all for a copy procedure, I get
the same message - 'This command cannot be performed with multiple
selections'

Martin Ellis

 

[Gord Dibben]

After you have filtered hit F5>Special>Visible cells only>OK

Right-click anywhere on the highlighted range and Copy then paste to your new
sheet.

Gord

 

[Guest]

Your efforts haven't been in vain Gord...I've managed to complete the copy
procedure with all three columns after filtering the 'speed' column to show
only ones with -30 at the end.
What's now baffling me is that the total number of rows shown on the left
before filtering was 5873, then after filtering it drops to 3782...but when I
select an entire column and paste it into a new book, there are only 2414
rows.


Martin Ellis

 

[Stan Brown]

Sun, 16 Sep 2007 08:46:00 -0700 from Martin Ellis

his is is what I've been doing, but when I choose 'Copy' a box appears which
says 'This command cannot be used on multiple selections'

Sort first, so that the lines to be copied are continuous.

You need not save the worksheet after sorting, if you want to
preserve the original order.