Referencing Variable Name Worksheets
 

I'm updating a formatted worksheet every day. I have a macro that
processes, lets the user augment, and then saves that days data into a new
worksheet with that days date. I need to reference that worksheet the next
business day to error check. I cannot :
PREVIOUS DATE IS IN CELL A1

s = range("a1").value
worksheets(s).select

Thank you for any help/suggestions

Chris Cameron

You might try
sheets(1)
sheet1

--
Don Guillett
SalesAid Software

"camerons" wrote in message
...
I'm updating a formatted worksheet every day. I have a macro that
processes, lets the user augment, and then saves that days data into a new
worksheet with that days date. I need to reference that worksheet the
next
business day to error check. I cannot :
PREVIOUS DATE IS IN CELL A1

s = range("a1").value
worksheets(s).select

Thank you for any help/suggestions

Chris Cameron

Unfortunately that does not seem to help. Due to manipulation by the user,
I don't know what order the sheets will be shown. The only consistent info
I have is that the sheet will be named what day it was worked on. I need my
code to reference that variable name and error check.

Is there any way for SheetOLD.select to accept OLD as a variable name?

ok here is how i do it.cell a1 has the formula=text(B2,d,mmm,yyyy).I type the
date in cell b2 and the answer is 8,may,2005,so the worksheet tab naming
regime has to match the format of the date or visa versa.(ie the tab has to
be 8,may,2005)Your original code then works a treat.
--
hope this helps
Paul


"camerons" wrote:

Unfortunately that does not seem to help. Due to manipulation by the user,
I don't know what order the sheets will be shown. The only consistent info
I have is that the sheet will be named what day it was worked on. I need my
code to reference that variable name and error check.

Is there any way for SheetOLD.select to accept OLD as a variable name?

Sub gotosheet()
'goes to sheet1 in order in vba
'no matter what you name it
Sheet1.Select
End Sub
Sub gotosheet1()
'goes to the first TAB as sorted
'no matter what you name it
Sheets(1).Select
End Sub

--
Don Guillett
SalesAid Software

"paul" . wrote in message
...
ok here is how i do it.cell a1 has the formula=text(B2,d,mmm,yyyy).I type
the
date in cell b2 and the answer is 8,may,2005,so the worksheet tab naming
regime has to match the format of the date or visa versa.(ie the tab has
to
be 8,may,2005)Your original code then works a treat.
--
hope this helps
Paul


"camerons" wrote:

Unfortunately that does not seem to help. Due to manipulation by the
user,
I don't know what order the sheets will be shown. The only consistent
info
I have is that the sheet will be named what day it was worked on. I
need my
code to reference that variable name and error check.

Is there any way for SheetOLD.select to accept OLD as a variable name?

First, thank you very much for trying to help.

I don't think I made myself too clear--my fault.

The problem is that the users want to keep a daily record of the report on a
seperate tab in the workbook. This means that the last daily sheet saved
that I have to check will be sheet 2 today, sheet 3 tommorow, and so on.
Thats why I need to get the Sheets command to accept some variable of
reference. Unfortunately Sheets(variable name).select does not seem to work
in any format. Is this even possible?

The VBA project viewer shows the sheets and their names, is there a way to
reference the code names as a variable?

I am currently using a workaround where I have a "static-named" "Archive"
worksheet that the previous days data gets stored to. When the new days
data gets refreshed then the archive data gets saved to a seperate file with
the name of the workday it was made, and the archive sheet gets updated.
This forces an extra step in the process, but until I figure out how to
select a worksheet named with a variable..............

yes but camerons your variable s does work,it is the date that causes the
trouble,see my post,it works for me.You can change the format of the date to
suit the format you use.You will have found that you cant use/in the tab
--
hope this helps
Paul


"camerons" wrote:

I am currently using a workaround where I have a "static-named" "Archive"
worksheet that the previous days data gets stored to. When the new days
data gets refreshed then the archive data gets saved to a seperate file with
the name of the workday it was made, and the archive sheet gets updated.
This forces an extra step in the process, but until I figure out how to
select a worksheet named with a variable..............

you can do this
myvariable = 'your variable here
Sheets(myvariable).Select 'no quotes required
But if I understand correctly you need the variable to be available AFTER
the worksheet has been modified and saved and closed?

--
hope this helps
Paul


"camerons" wrote:

I am currently using a workaround where I have a "static-named" "Archive"
worksheet that the previous days data gets stored to. When the new days
data gets refreshed then the archive data gets saved to a seperate file with
the name of the workday it was made, and the archive sheet gets updated.
This forces an extra step in the process, but until I figure out how to
select a worksheet named with a variable..............