Changing Date Format's

[BexleyManor]

It's one of those Monday mornings where the head refuses to get into gear!!

Can somebody, anybody point me in the right direction??



Private Sub ADDSHT_Click()
Dim icount As Integer
icount = Worksheets.Count
Worksheets(1).Copy after:=Worksheets(icount)
ActiveSheet.Name = Format(Month(Now) & Year(Now), "mmm yy")
End Sub


The above is resulting in the worksheet being named Jan 34 as opposed to Dec 04???!!! :dunno

Your help would be gratefully received.... :yes

[Jacob Hilderbrand]

The month & year is not being recognized as a date. But there is no need to go to that much trouble. You can just use Now by itself, or Date. Also Long is faster than Integer so it should always be used.


Option Explicit

Private Sub ADDSHT_Click()
Dim icount As Long
icount = Worksheets.Count
Worksheets(1).Copy After:=Worksheets(icount)
ActiveSheet.Name = Format(Date, "mmm yy")
End Sub

[BexleyManor]

Great work Jacob, works perfectly!

Many Many thanks

[BexleyManor]

Actually, I'm now getting error messages, "Copy method Of worksheet class failed"??

Any ideas why???

[Jacob Hilderbrand]

Not sure. Can you attach the workbook?

[BexleyManor]

Hi Jacob, wookbook attached as requested. I'm simply trying to Copy the first sheet (Template) after the last sheet and name it after the current month and year.

Hope this makes sense??

[Jacob Hilderbrand]

The attached workbook works fine for me with Excel 2002. Try this code instead and see if it works.



Option Explicit

Private Sub AddSht_Click()
Sheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(Date, "mmm yy")
End Sub

[BexleyManor]

Hi Jacob,

I'm on Excel 97 at the moment.

The code seems to work fine, then the next time I run it I get the error message??

Driving me nuts!

I can see no reason for this behavior??

[mark007]

Are you trying to name it the same name as an existing sheet?
:)

[BexleyManor]

Hi Mark,

In theory no!

However, if the user clicked the 'add sheet' button twice in the same session then this sort of error would occur.

The problem seems to lie with the line:

Worksheets("Template").copy After:=Worksheets(icount)

As I said before. Sometimes the code works fine, others it throws up the error??!!

[BexleyManor]

To make matters even stranger, I used the macro recorder to see if that would show me where It's going wrong. It created practically the same code as we've written, worked twice then started throwing up the error messages??!!

I'm going to fix my PC with a hammer shortly!!

[Jacob Hilderbrand]

97 is pretty unstable. At least upgrade to 2000 if you can.

[johnske]

Hi Bexley,

Try this mod to Jacobs code & see if it clears it up...(it should be ok to run the code twice in the same session then)


Option Explicit

Private Sub AddSht_Click()
Sheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(Date, "mmm yy") & " (" & Sheets.Count & ")"
End Sub

[mark007]

In theory no!

However, if the user clicked the 'add sheet' button twice in the same session then this sort of error would occur.


I'm confused, is that not what you're doing:


The code seems to work fine, then the next time I run it I get the error message??

:)

[mark007]

Another alternative would be:



Private Sub AddSht_Click()
on error resume next
dim ws as worksheet
set ws=thisworkbook.worksheets(Format(Date, "mmm yy"))
on error goto 0
if ws is nothing then
Sheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(Date, "mmm yy")
end if
End Sub

:)

[BexleyManor]

Hi folks,

Firstly, great big thanks to all of you for your time and input, much appreciated.

I've now tried all of your suggestions but still get sporadic 'Copy method of worksheet class failed' messages. Totally confusing as one mintue the code works, then the next it fails?? Having tried all your code at home on Office 2003 with no problems I suspect either my work copy of 97 is banjo'ed or We've discovered some obscure Excel glitch. Whatever the case it looks like I will have to wait until the new year until our beloved IT dept. decide to roll out Office 2003.

Thanks again folks.

[BexleyManor]

I think I know what's causing the error, or at least seems to be.

If I fire the code and the template sheet is the active sheet I get the error. When any other sheet is selected and the code is fired it works.

Could this be down to the command button being on the template sheet??

[Killian]

You should test this... remove the button, and change:

Private Sub AddSht_Click()
to:

Sub AddSht()
then run the macro from the VBA toolbar or Tools|Macros a few times.
I've had problems with Activex controls on worksheets in 97 but I don't remember any since moving to XP and 2003

[Jacob Hilderbrand]

Well you could always have VBA activate another sheet before copying.


Option Explicit

Private Sub AddSht_Click()
Sheets(2).Activate
Sheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(Date, "mmm yy")
End Sub

[BexleyManor]

Either of the above options sound good. My users are wary of change and the thought of using a button on the toolbar as opposed to the sheet may fill them with fear!!

Still, the damn muppets will have to get over it!!

Thanks guys

[Ken Puls]

My users are wary of change and the thought of using a button on the toolbar as opposed to the sheet may fill them with fear!!
:D Bexley, did you steal some of my users? :rofl

[johnske]

You should test this... remove the button, and change:
Private Sub AddSht_Click()
to:
Sub AddSht()
then run the macro from the VBA toolbar or Tools|Macros a few times.
I've had problems with Activex controls on worksheets in 97 but I don't remember any since moving to XP and 2003You could also do as suggested by Killian (above) but use the buttons from the "forms" toolbar on the sheet...

[Jacob Hilderbrand]

:D Bexley, did you steal some of my users? :roflHis users are probably the ones that trained mine. I can't even get all of them to use the Tab key in Word to indent instead of multiple spaces.

Then there was the 10 minute phone conversation I had trying to get someone to just press Esc. That's it, I said press the Escape key and then... Well I never got any further.

[Brandtrock]

Bexley, did you steal some of my users?
His users are probably the ones that trained mine. I can't even get all of them to use the Tab key in Word to indent instead of multiple spaces.

Then there was the 10 minute phone conversation I had trying to get someone to just press Esc. That's it, I said press the Escape key and then... Well I never got any further.His users are probably the ones that trained mine. I can't even get all of them to use the Tab key in Word to indent instead of multiple spaces.

Then there was the 10 minute phone conversation I had trying to get someone to just press Esc. That's it, I said press the Escape key and then... Well I never got any further.

Complete empathy on this one guys. I used to have a CPA in one of my offices that insisted on using a space to "clear" last month's data from the data entry area. Even after I coded a "CLEAR" button for him he did it his way. Of course I got calls every month end telling me my spreadsheet was "broken" because the formulas didn't work.

:eek: