How do I reference the sheet named 20 (for the day of the month), then tomorrow the With Sheets("20") will be With Sheets("21")?

Sub Macro1()

With Sheets("20")
'.do stuff
End With


End Sub

On the worksheet in a cell =Today() and then custom formatted "d" returns 20.
Tomorrow I presume it will return 21.

How do I make that happen in the code?

Howard

Hi Howard,

Am Tue, 20 Jan 2015 03:04:49 -0800 (PST) schrieb L. Howard:

How do I reference the sheet named 20 (for the day of the month), then tomorrow the With Sheets("20") will be With Sheets("21")?

try:

With Sheets(CStr(Day(Date)))
'Do some stuff
End With


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

try:

With Sheets(CStr(Day(Date)))
'Do some stuff
End With


Regards
Claus B.\

Thanks, Claus.

Hmmm, new stuff for me. I find this as to describe CStr but very little on examples.

CStr Converts an expression to string data type


Is there a short "how it works" or is it best understood to "go to school" on the function?

Howard

Hi Howard,

Am Tue, 20 Jan 2015 04:12:33 -0800 (PST) schrieb L. Howard:

CStr Converts an expression to string data type

Sheets(20) is the 20th sheet in the workbook.
Sheets("20") is your sheet named by day.
Day(Date) is a numeric value and would refer to the 20th sheet.
So you have to convert Day(Date) to string.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Tuesday, January 20, 2015 at 4:20:13 AM UTC-8, Claus Busch wrote:
Hi Howard,

Am Tue, 20 Jan 2015 04:12:33 -0800 (PST) schrieb L. Howard:

CStr Converts an expression to string data type

Sheets(20) is the 20th sheet in the workbook.
Sheets("20") is your sheet named by day.
Day(Date) is a numeric value and would refer to the 20th sheet.
So you have to convert Day(Date) to string.


Regards
Claus B.
--

Okay, thanks. Along with that and this that I found, should keep me off the streets for awhile!


CStr and Date

The Date type always contains both date and time information. For purposes of type conversion, Visual Basic considers 1/1/0001 (January 1 of the year 1) to be a neutral value for the date, and 00:00:00 (midnight) to be a neutral value for the time. CStr does not include neutral values in the resulting string. For example, if you convert #January 1, 0001 9:30:00# to a string, the result is "9:30:00 AM"; the date information is suppressed. However, the date information is still present in the original Date value and can be recovered with functions such as DatePart.

Howard

Hi Howard,

Am Tue, 20 Jan 2015 04:27:18 -0800 (PST) schrieb L. Howard:

CStr and Date

The Date type always contains both date and time information. For purposes of type conversion, Visual Basic considers 1/1/0001 (January 1 of the year 1) to be a neutral value for the date, and 00:00:00 (midnight) to be a neutral value for the time. CStr does not include neutral values in the resulting string. For example, if you convert #January 1, 0001 9:30:00# to a string, the result is "9:30:00 AM"; the date information is suppressed. However, the date information is still present in the original Date value and can be recovered with functions such as DatePart.

Dim myDate As Date

myDate = #1/1/2001 9:30:00 AM#

MsgBox CStr(myDate)

The result is: "01.01.2001 09:30:00"


Dim myDate As Double

myDate = #1/1/2001 9:30:00 AM#

MsgBox CStr(myDate)

The result is:"36892.3958333333


Dim myDate As Double

myDate = #1/1/2001 9:30:00 AM#

MsgBox CStr(Day(myDate))

The result = "1" as well with dimension date and double


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Dim myDate As Date

myDate = #1/1/2001 9:30:00 AM#

MsgBox CStr(myDate)

The result is: "01.01.2001 09:30:00"


Dim myDate As Double

myDate = #1/1/2001 9:30:00 AM#

MsgBox CStr(myDate)

The result is:"36892.3958333333


Dim myDate As Double

myDate = #1/1/2001 9:30:00 AM#

MsgBox CStr(Day(myDate))

The result = "1" as well with dimension date and double


Regards
Claus B.


Now that's a plateful!

Howard

Not meaning to ad to your already "plate full", but I thought I'd share
a concept here that fits the topic.

The project is a single-file daily scheduler that uses a sheet template
for each day of a month. The task of the project is to 'schedule'
fitness instructors at a gym for assignments during their shift.

There are 12 workbooks named for a fiscal period of 1 calendar year.

The template workbook has 31 copies of the daily scheduler template
sheet.

Each day when the file is opened this happens...

Sub Auto_Open()
Dim wksToday As Worksheet
InitGlobals '//initialize global variables

'Setup ThisWorkbook.Name to match GymID
If Not InStr(ThisWorkbbok.Name, gsThisGym) Then
ThisWorkbook.SaveAs gsAppPath & gsThisGym _
& Format(Month(Date()), "Mmm")
& ThisWorkbook.Name
End If

Set wksToday = Sheets(Day(Date())
With wksToday: .Visible = True: .Activate: End With
InitDailyWks '//setup default sheet info
End Sub

...which only activates the appropriate sheet on/for days the gym is
open.

The InitDailyWks routine inserts the date field on the template with a
long date format ("day Month dd, yyyy"), and fills in the gym location
details.

The InitGlobals routine loads values into global scope variables used
by a project. This and the variable declarations reside in the same
module as the AutoMacros, replacing the Workbook_Open event...

Sub InitGlobals()
gsAppPath = ThisWorkbook.Path & "\"
If Not bNameExists("GymID") Then Setup_ThisGym
gsThisGym = "GymID" _
& Mid(ThisWorkbook.Names("GymID").RefersTo, 2) & "_"
End Sub


Function bNameExists(sName$, Optional Wks As Worksheet) As Boolean
' Checks if sName exists in Wks
' Arguments:
' sName The defined name to check for
' Wks A ref to the Wkb or Wks being checked
' Returns:
' True if name exists

Dim x As Object
If Wks Is Nothing Then Set Wks = ActiveSheet
On Error Resume Next
Set x = Wks.Names(sName): bNameExists = (Err = 0)
End Function

...where InitGlobals also validates globals if their values aren't set.

The Setup_ThisGym routine creates local scope defined names for each
template sheet for "GymID" and "GymInfo". (This info is provided by the
user at 1st startup only) The names are local scope so the head office
can merge daily sheets for each month in a consolidation file for the
year, without raising name conflicts. Also, each gym has its own sheet
in the merged file, and each month gets a row in an outlined area for
that month.

Fortunately, this client only has 2 locations and so managing things
this way works for them. I propose a different approach if they add
more locations whereby the project converts to an addin that uses a
single daily scheduker worksheet template and starts new workbooks
based on month name for any calendar year. This would greatly simplify
ongoing project management/maintenance. Also, the distributed addin
won't require any head office code, making that a separate addin that
can easily be made a 'plugin' to the main addin installed at head
office if it needs its own scheduler. This, of course, changes the
project structure so it's no longer a single-file solution!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion