Destination formatting 2000?

M

Mex

Excel 2003 has the function to match destination formatting when pasting in
to a sheet.
How can I do this in 2000?
 
M

Mex

Unfortunatly I am pasting in from internet explorer.
I'll explain my problem further.

I am pasting in numbers with 0's at the beginning. Therefore I loose the
0's. I need to be able to paste in to the document without loosing the 0's
and without going back after the paste to add the 0's.

Thanks
 
M

Mex

Unfortunatly when you format the cells, then paste in to them the formatting
changes.

If you do this from within excel it will work fine, but I am pasting from
internet explorer.

I wrote a small macro to go back and change the formatting once I have
pasted, but this still does not have the desired affect.

Say I had a number "0044" upon pasting it would change to 44, then I would
run the macro which would change the field in to "special" which was set to
0000. The number will appear to be 0044, but if you look at the formula bar,
you will see that it is actuall still only 44!!!!

Still trying..
 
J

JMay

In a standard module enter:

Sub FillDigits()
'This macro converts cell values from numbers (as General format) to
Text.
'It Converts only a single column of cells at a time
'Before Running this macro make sure you have selected
'the first (top-most) cell in the column to be converted.
'the macro will convert each cell (downward) until it reaches
'a blank cell and will stop
'Current setting below allows for 5 full digits (including your number)

r = ActiveCell.Row
c = ActiveCell.Column
While Not IsEmpty(Cells(r, c))
celVal = Trim(Cells(r, c))
Cells(r, c).NumberFormat = "@"
Cells(r, c) = Application.Rept("0", 5 - Len(celVal)) & celVal
r = r + 1
Wend

End Sub

Hope this helps,

Jim May
 
M

Mex

Thank you JMay, I think we may be getting somewhere, however my inserted text
is horizontal ie. a1, b1, c1 etc.. and the macro didnt seem to work as I
think it decends down a column?!?

Any Ideas?
 
Top