On Friday, May 30, 2014 7:16:05 PM UTC-7, GS wrote:
No! Do it this way...



Set wksTarget = Sheets(Format(Date, "mmm yy")) 'Jan,Feb...



OR



Set wksTarget = Sheets(Format(Date, "mmmm yy")) 'full name



--

Garry

The 14 is a day not the year. You were referring to 2014 I think.

I'm thinking name change is a good idea.

Howard

So what's happening is the sheetnames reflect 'period' data. In this
case I'd go with modifying CodeName and use that so it won't matter
what the sheetname is. For example...

Sheets("Feb 14").CodeName = "wksFeb14"

...and use it like this...

<snip
Dim sName$
Const sPeriodEnd$ = "14" '//edit to suit
sName = Get_SheetTabName("wks" & Format(Date(), "mmm" & sPeriodEnd))
If sName < "" then Set wksTarget = Sheets(sName)
</snip


Function Get_SheetTabName$(CodeName$, Optional Wkb As Workbook)
Dim wks As Worksheet
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
For Each wks In Wkb.Worksheets
If wks.CodeName = CodeName Then
Get_SheetTabName = wks.name: Exit Function
End If
Next
End Function

Better alternative:
Store CodeName in a local scope defined name. This is my preference
since Excel has been known to discard user-defined values and replace
with default values after crash recovery. In this case you just pull
from wks.Names like this...

<snip
Dim sName$
Const sPeriodEnd$ = "14" '//edit to suit
sName = Get_uiCodename("wks" & Format(Date(), "mmm" & sPeriodEnd))
If sName = "" Then Exit Sub
Set wksTarget = Sheets(sName)
</snip

Function Get_uiCodename$(CodeName$, Optional Wkb As Workbook)
Dim wks As Worksheet, vTmp, sDefName$
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
On Error Resume Next '//if name doesn't exist
For Each wks In Wkb.Worksheets
sDefName = "uiCodename"
vTmp = wks.Names(sDefName).RefersTo
If Not (vTmp = Empty) Then
sDefName = "'" & wks.name & "'!" & sDefName
vTmp = Application.Evaluate(sDefName)
If (vTmp = CodeName) Then
Get_uiCodename = wks.name: Exit Function
End If '(vTmp = CodeName)
End If 'Not (vTmp = Empty)
Next
End Function

--
Garry

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