SPreadsheets formatting

[Mars]

Is there a way programmatically correct Excel spreadsheets
formatting? I have an Excel Workbook which contains many
spreadsheets invoices, that have the same form and same
formulas. Problem is thatt some of spreadsheets invoices
are created a little inaccuratly, i.e. some cells with
value and formulas are displaced relatively the previous
spreadsheet cells. Example: some spreadsheets have q-
ty, sum, total located in other cell relatively the
previous spreadsheet. Does it possible 'normalize'
formatting of all spreadsheets without alteration of each
sheet separately? For example, set some sheet as a sample
and automatically format all others spreadsheets the same
way? (withous loss anu data)

 

[Gary Thomson]

Yes.

Click on the very top left of the sheet (i.e. on the
rectangle to the left of the "A" column and just above
the "1" row)that is your sample (i.e. is formatted
correctly). This will highlight the whole sheet.

Now do "Ctrl + C" to copy this.

Now, go to one of the sheets you want to copy these
formats onto. After clicking the tab for this sheet, hold
down CONTROL, and click on the "Tabs" of the other sheets
you also want formatted (a quicker way of doing this is to
click on the first sheet, hold down SHIFT, and click on
the last sheet, but note that this selects all sheets in
between). All the sheets that you want formatted should
now have their "Tabs" shown in White.

Now, click the little rectangle at the very top left of
the sheet, but this time do it with the RIGHT mouse
button. This will bring up a list. Click "Paste
Special". Now, in the box that appears, highlight the
bullet next to "Formats", click "OK", and all your new
sheets will be formatted exactly as the sample sheet, but
still retain each sheet's values and formulae.

Happy to help,

Gary Thomson.

 

[Guest]

Thanks Gary,

some amplification: in "Paste Special" box I should select
only "Formats" radiobutton?

Thanks you,
Alex

 

[Gord Dibben]

Gary's suggestion will replicate formatting(font, width, height etc.) of all
sheets, but will not move any "relatively displaced" cells around to match the
location on the sample sheet.

I'm afraid you are in for a lot of manual moving/copying of cells.

Gord Dibben Excel MVP

 

[Guest]

Yes, its clear. I just need specify in Gary's suggestion:
need I select only "Formats" radiobutton in "Paste
Special" box?

Thanks

 

[Gord Dibben]

If all you want to copy is the format of the cells, just hit the option button
for Paste Special>Formats.

Gord