macro help
 

Hi,

I have a macro that completes lots of stuff for me from a particular
worksheet in another workbook.

I want this part of my code....

Windows("HXTCNC DATA FOR THU 30-NOV-06.XLS").Activate

to be replaced with code that will automaticaly open the workbook with the
same name ie 'HXTCNC DATA FOR' but for TODAY()+1 in the format dd-mmm-yy.

For example if I ran the macro now it would look for the workbook
named.......

HXTCNC DATA FOR MON 04-DEC-06.XLS

Thanks in advance

macro help
 

I would create a string variable to hold the workbook name like

Dim s as Strin
s = "HXTCNC DATA FOR " & Format(Date+1,"ddd dd-mmm-yy") & ".xls
Workbooks(s).Activat

The workbooks() doesn't require a case sensitive argument, but yo
could wrap the assignment in Ucase if you wanted the s variable to hol
an all upper case string

--
RichardScholla
-----------------------------------------------------------------------
RichardSchollar's Profile: http://www.officehelp.in/member.php?userid=524
View this thread: http://www.officehelp.in/showthread.php?t=127744

Posted from - http://www.officehelp.i

macro help
 

This can be done in one step, but its easier to follow this way:

s = Now + 1
s1 = UCase(Application.WorksheetFunction.Text(s, "ddd")) & " "
s2 = UCase(Application.WorksheetFunction.Text(s, "dd-mmm-yy"))
s3 = "HXTCNC DATA FOR " & s1 & s2 & ".XLS"
Windows(s).Activate

--
Gary's Student


"Anthony" wrote:

Hi,

I have a macro that completes lots of stuff for me from a particular
worksheet in another workbook.

I want this part of my code....

Windows("HXTCNC DATA FOR THU 30-NOV-06.XLS").Activate

to be replaced with code that will automaticaly open the workbook with the
same name ie 'HXTCNC DATA FOR' but for TODAY()+1 in the format dd-mmm-yy.

For example if I ran the macro now it would look for the workbook
named.......

HXTCNC DATA FOR MON 04-DEC-06.XLS

Thanks in advance

macro help
 

Thanks for the explanation -works well !

"Gary''s Student" wrote:

This can be done in one step, but its easier to follow this way:

s = Now + 1
s1 = UCase(Application.WorksheetFunction.Text(s, "ddd")) & " "
s2 = UCase(Application.WorksheetFunction.Text(s, "dd-mmm-yy"))
s3 = "HXTCNC DATA FOR " & s1 & s2 & ".XLS"
Windows(s).Activate

--
Gary's Student


"Anthony" wrote:

Hi,

I have a macro that completes lots of stuff for me from a particular
worksheet in another workbook.

I want this part of my code....

Windows("HXTCNC DATA FOR THU 30-NOV-06.XLS").Activate

to be replaced with code that will automaticaly open the workbook with the
same name ie 'HXTCNC DATA FOR' but for TODAY()+1 in the format dd-mmm-yy.

For example if I ran the macro now it would look for the workbook
named.......

HXTCNC DATA FOR MON 04-DEC-06.XLS

Thanks in advance