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

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?

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

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 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

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

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