Hi, thanks in advance for anyone who can help.

When I run the above command embedded in a macro it does the copy sheet
command sucessfully but loses the number format of the original.

When I use the Excel edit move or copy sheet to do the same thing it
preserves the number format (which is a custom format).

I really need to preserve the original number formats. Anyone have any
ideas?


--
tmbo
------------------------------------------------------------------------
tmbo's Profile: http://www.excelforum.com/member.php...o&userid=37214
View this thread: http://www.excelforum.com/showthread...hreadid=569306

I couldn't duplicate this in xl2003.

What version of excel are you using and what's the custom number format?

tmbo wrote:

Hi, thanks in advance for anyone who can help.

When I run the above command embedded in a macro it does the copy sheet
command sucessfully but loses the number format of the original.

When I use the Excel edit move or copy sheet to do the same thing it
preserves the number format (which is a custom format).

I really need to preserve the original number formats. Anyone have any
ideas?

--
tmbo
------------------------------------------------------------------------
tmbo's Profile: http://www.excelforum.com/member.php...o&userid=37214
View this thread: http://www.excelforum.com/showthread...hreadid=569306

--

Dave Peterson

Hi Dave,

Thanks for the reply.

I am using 2003 and the number format I am using is:

_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)

So if you have a sheet with a custom number format as above and then
use

Edit move or copy sheet

with
To Book: (New Book)

and Create a copy ticked

It will keep the format. If at the same time you record the macro to do
that process and then play that macro it will lose the format...very
frustrating!!!

Hope you can help.

Thanks Tim


--
tmbo
------------------------------------------------------------------------
tmbo's Profile: http://www.excelforum.com/member.php...o&userid=37214
View this thread: http://www.excelforum.com/showthread...hreadid=569306

I still couldn't replicate it.

I used this code to test:

Option Explicit
Sub testme()

Dim wks As Worksheet
Set wks = Worksheets("sheet1")

With wks
.Range("a1").NumberFormat _
= "_(* #,##0_);_(* (#,##0);_(* "" - ""_);_(@_)"
Debug.Print "Befo " & .Range("A1").NumberFormat
.Copy
End With

Debug.Print "After: " & ActiveSheet.Range("a1").NumberFormat
End Sub

And pasting from the immediate window:

Befo _(* #,##0_);_(* (#,##0);_(* " - "_);_(@_)
After: _(* #,##0_);_(* (#,##0);_(* " - "_);_(@_)

And when I used .Move (instead of .Copy), I got this:

Befo _(* #,##0_);_(* (#,##0);_(* " - "_);_(@_)
After: _(* #,##0_);_(* (#,##0);_(* " - "_);_(@_)

I don't know if this has anything to do with your problem, but this formatting
is the same as my Accounting with 0 decimals and no currency symbol. Is there
anything special with your accounting format?

Are you using USA settings?

What happens if you try the code I used? How about with a brand new workbook?

(I don't have a guess why it wouldn't work for you.)



tmbo wrote:

Hi Dave,

Thanks for the reply.

I am using 2003 and the number format I am using is:

_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)

So if you have a sheet with a custom number format as above and then
use

Edit move or copy sheet

with
To Book: (New Book)

and Create a copy ticked

It will keep the format. If at the same time you record the macro to do
that process and then play that macro it will lose the format...very
frustrating!!!

Hope you can help.

Thanks Tim

--
tmbo
------------------------------------------------------------------------
tmbo's Profile: http://www.excelforum.com/member.php...o&userid=37214
View this thread: http://www.excelforum.com/showthread...hreadid=569306

--

Dave Peterson

Hi Dave,

To replicate it perhaps create a new number format in your custom list
that is not included in the template used to open a new sheet.

Unfortunately the code didn't work.

I think the problem with what I am doing is that when the code runs
from the macro it opens a new worksheet from a template that doesn't
have the format as a custom format.

I have to post the spreadsheet out to many users so I don't think it is
practical to get everyone to change their templates.

Still it is strange that when I run the same command from the Excel
menu it doesn't lose the formatting...can't quite work that one out.

I wonder if there is some other way I can around it...?


Cheers

Tim


--
tmbo
------------------------------------------------------------------------
tmbo's Profile: http://www.excelforum.com/member.php...o&userid=37214
View this thread: http://www.excelforum.com/showthread...hreadid=569306

I don't have another guess,

Sorry.

tmbo wrote:

Hi Dave,

To replicate it perhaps create a new number format in your custom list
that is not included in the template used to open a new sheet.

Unfortunately the code didn't work.

I think the problem with what I am doing is that when the code runs
from the macro it opens a new worksheet from a template that doesn't
have the format as a custom format.

I have to post the spreadsheet out to many users so I don't think it is
practical to get everyone to change their templates.

Still it is strange that when I run the same command from the Excel
menu it doesn't lose the formatting...can't quite work that one out.

I wonder if there is some other way I can around it...?

Cheers

Tim

--
tmbo
------------------------------------------------------------------------
tmbo's Profile: http://www.excelforum.com/member.php...o&userid=37214
View this thread: http://www.excelforum.com/showthread...hreadid=569306

--

Dave Peterson

Hi Dave,

I attached a spreadsheet with an example.

If you run the macro in the this workbook you should see it revert to
the minus signs.

Cheers

Tim


--
tmbo
------------------------------------------------------------------------
tmbo's Profile: http://www.excelforum.com/member.php...o&userid=37214
View this thread: http://www.excelforum.com/showthread...hreadid=569306

I don't connect through excelforum, so I don't see the attachment. And I don't
open attachments anyway.

tmbo wrote:

Hi Dave,

I attached a spreadsheet with an example.

If you run the macro in the this workbook you should see it revert to
the minus signs.

Cheers

Tim

--
tmbo
------------------------------------------------------------------------
tmbo's Profile: http://www.excelforum.com/member.php...o&userid=37214
View this thread: http://www.excelforum.com/showthread...hreadid=569306

--

Dave Peterson