Sheets("Rpt").Copy different results from running in macro than off excel menu??

[tmbo]

Hi, thanks in advance for anyone who can help.

When I run the above command embedded in a macro it does the copy shee
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 i
preserves the number format (which is a custom format).

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

 

[Dave Peterson]

I couldn't duplicate this in xl2003.

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

 

[tmbo]

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

 

[Dave Peterson]

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 "Before: " & .Range("A1").NumberFormat
.Copy
End With

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

And pasting from the immediate window:

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

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

Before: _(* #,##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]

Hi Dave,

To replicate it perhaps create a new number format in your custom lis
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 run
from the macro it opens a new worksheet from a template that doesn'
have the format as a custom format.

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

Still it is strange that when I run the same command from the Exce
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

Ti

 

[tmbo]

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

 

[Dave Peterson]

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

 

[Dave Peterson]

I don't have another guess,

Sorry.

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