Excel format

[Kitty]

Hi there,
I am having trouble working with Excel. Here is what
happens. My master spreadsheet is very big: 123 rows and
7 columns. I need to divide the master spreadsheet into 8
separete (smaller) spreadsheets to send those smaller
spreadsheets to 8 different people. The problem is when I
try to first sort the master spreadsheet by peoples' name
and then copy and paste the sorted section into a brand
new spreadsheet, Excell messes up the format. I have been
struggling spending numerous hours on just trying to
format that monster. The only solution I found is to save
the master spreadsheet 8 times and delete sections that
don't apply, which makes the process extremely time
consuming and prone to error. Is there a better way to
manage large spreadsheets like that? I tried the Help
section, but it did not seem to really apply to this
issue.
Any help is greatly appreciated.
Thank you

 

[JulieD]

Hi Kitty

what do you mean by "messes up the format"
and what version of excel are you using?

Cheers
JulieD

 

[Gord Dibben]

Kitty

Take this as an attempt to inform, not to belittle.

7 columns by 123 rows is a tiny workbook.

Probably only about 25kb depending upon complexity.

A large workbook would be 50MB.

Further to Julie's post.........are you selecting all columns before sorting
on the names.

Do not rely on Excel to get the sort correct all the time.

When you copy the sorted results to a new sheet in a new workbook, perhaps do
it in two steps.

First copy the data then copy again using Edit>Paste Special>Formats.


Gord Dibben Excel MVP

 

[Fadi Chalouhi]

Hi Kitty, JulieD,

I think I understand what Kitty means : what you do a sort on an excel
table, the format actually moves with the cell... so for example if
you're using the alternating row format (yellow, white, yellow, white)
and then you sort the rows in the table you might end up with a
"messed up" format where the colors are no longer alternating.

What I usually do in these cases is copy the data into ONE other
sheet, sort the data and then paste it back in the original table.
this keeps the format intact.

I'd be interested to know if there is any othe way of doing it (other
than through VBA)

HTH

Fadi

 

[JulieD]

Hi Fadi

if you use CONDITIONAL FORMATTING to do the row banding, sorting won't mess
it up ... this can be done:

select the rows (using the row numbers) of your data area and choose
format / conditional formatting
choose
formula is
and type
=MOD(ROW()-1,2)+1<=1

click the format button, go to the patterns tab, choose a colour and click
OK twice

this will format all ODD rows to the chosen background

 

[David McRitchie]

One way would be to copy the entire sheet and delete the parts
from the copy that you don't want.

If you just use Copy (Ctrl+C) and then paste, you are not
picking up column width for example.