Month function not selecting correct month

[hughG]

Intel Imac, Office 2004 OSX 10.5.6

Hi all

I have written a macro which selects a worksheet, named with the
current month
Workbooks("anyworkbook.xls").Sheets(Month(Now)).Select
When it runs it selects the worksheet two months previously.
If I run
Workbooks("anyworksheet.xls").Sheets(Month(Now) + 2).Select
it selects the correct month.
My system date is correct (set by Apple)

Any ideas

Many thanks

 

[Niek Otten]

Month terurns a number, not a text. Si selects the n-th worksheet, no matter
what name.

Use

Workbooks("anyworksheet.xls").Sheets(format(Month(Now),"@")).Select

instead

 

[hughG]

Month terurns a number, not a text. Si selects the n-th worksheet, no matter
what name.

Use

Workbooks("anyworksheet.xls").Sheets(format(Month(Now),"@")).Select

instead

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

Dear Niek thank you for your reply

I have tried this and get a "subscript out of range" error
I don't understand why this should work but I would be very interested
to learn

Many thanks
Hugh

 

[Niek Otten]

Hi High,

How did you name your sheets? A macro? Manually?
What is the January sheet called: 1, Jan, january or something else?
How are the sheets ordered?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

Month terurns a number, not a text. Si selects the n-th worksheet, no
matter
what name.

Use

Workbooks("anyworksheet.xls").Sheets(format(Month(Now),"@")).Select

instead

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

Dear Niek thank you for your reply

I have tried this and get a "subscript out of range" error
I don't understand why this should work but I would be very interested
to learn

Many thanks
Hugh

 

[hughG]

Hi High,

How did you name your sheets? A macro? Manually?
What is the January sheet called: 1, Jan, january or something else?
How are the sheets ordered?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel






Dear Niek thank you for your reply

I have tried this and get a "subscript out of range" error
I don't understand why this should work but I would be very interested
to learn

Many thanks
Hugh

Hi Niek

The sheets are already manually named January 2009, February 2009 etc.
in date order. I have tried using just the month name eg. January but
get the same error message

Regards

Hugh

 

[JE McGimpsey]

The sheets are already manually named January 2009, February 2009 etc.
in date order. I have tried using just the month name eg. January but
get the same error message

The generated string needs to match your sheet name exactly (VBA doesn't
have any native intelligence to know that 1/09 is the same as January
2009, etc.).

To VBA, a date is just an integer offset from a base date (i.e, Jan 1,
2009 = 39814 days after 12/31/1899). It's only by formatting the date
into the appropriate string that you can get the date in words.

For instance:

Workbooks("anyworksheet.xls").Sheets(Format(Date, "mmmm yyyy")).Select

will select (this month) a sheet named "January 2009".

 

[hughG]

The generated string needs to match your sheet name exactly (VBA doesn't
have any native intelligence to know that 1/09 is the same as January
2009, etc.).

To VBA, a date is just an integer offset from a base date (i.e, Jan 1,
2009 = 39814 days after 12/31/1899). It's only by formatting the date
into the appropriate string that you can get the date in words.

For instance:

   Workbooks("anyworksheet.xls").Sheets(Format(Date, "mmmm yyyy")).Select

will select (this month) a sheet named "January 2009".

Thank you for your help, it works.
Also thank you for your explanation, helps to understand things in the
future

Regards

Hugh