The following code creates a file name. How do I change
this as a public statement in order to use wherever i want
to use the file name in the same workbook? I tried
something like public Myfilename , but it doesn't works?
Any help highly appreciated?


FlName = Format(Sheets("summary").Range("rptdate") -
1, "mmm dd")
Mydir = "C:\Daily Reports\"
If WeekDay(Sheets("summary").Range("rptdate")) =
vbMonday Then
myfile = Format(Sheets("summary").Range("rptdate") -
3 - Range("holid"), mmm dd")
Else
myfile = Format(Sheets("summary").Range("rptdate") -
Range("holid") - 1, "mmm dd")
End If
FileName = Mydir & "[" & myfile & "]"

Public FileName as String

What's the issue -- why do you say this doesn't work?


"mac" wrote in message
...
The following code creates a file name. How do I change
this as a public statement in order to use wherever i want
to use the file name in the same workbook? I tried
something like public Myfilename , but it doesn't works?
Any help highly appreciated?


FlName = Format(Sheets("summary").Range("rptdate") -
1, "mmm dd")
Mydir = "C:\Daily Reports\"
If WeekDay(Sheets("summary").Range("rptdate")) =
vbMonday Then
myfile = Format(Sheets("summary").Range("rptdate") -
3 - Range("holid"), mmm dd")
Else
myfile = Format(Sheets("summary").Range("rptdate") -
Range("holid") - 1, "mmm dd")
End If
FileName = Mydir & "[" & myfile & "]"

The issue is when I call this in another sub it doesn't
goes to that: This what I am doing

sub DataUpdate()
Sheets("Sheet2").Select
ActiveSheet.Range("AD5").Select
ActiveCell.Formula = ActiveCell.Formula = "=VLOOKUP
(I4,'" & filename & "Sheet2'!Sectdata,14,FALSE)"
end sub

Here it doesn't takes my file name?


-----Original Message-----
Public FileName as String

What's the issue -- why do you say this doesn't work?


"mac" wrote in
message
...
The following code creates a file name. How do I change
this as a public statement in order to use wherever i
want
to use the file name in the same workbook? I tried
something like public Myfilename , but it doesn't works?
Any help highly appreciated?


FlName = Format(Sheets("summary").Range("rptdate") -
1, "mmm dd")
Mydir = "C:\Daily Reports\"
If WeekDay(Sheets("summary").Range("rptdate")) =
vbMonday Then
myfile = Format(Sheets("summary").Range
("rptdate") -
3 - Range("holid"), mmm dd")
Else
myfile = Format(Sheets("summary").Range
("rptdate") -
Range("holid") - 1, "mmm dd")
End If
FileName = Mydir & "[" & myfile & "]"



.

Try

FileName = Mydir & "[" & myfile & ".xls" & "]"

--
Regards,
Tom Ogilvy

"mac" wrote in message
...
The issue is when I call this in another sub it doesn't
goes to that: This what I am doing

sub DataUpdate()
Sheets("Sheet2").Select
ActiveSheet.Range("AD5").Select
ActiveCell.Formula = ActiveCell.Formula = "=VLOOKUP
(I4,'" & filename & "Sheet2'!Sectdata,14,FALSE)"
end sub

Here it doesn't takes my file name?


-----Original Message-----
Public FileName as String

What's the issue -- why do you say this doesn't work?


"mac" wrote in
message
...
The following code creates a file name. How do I change
this as a public statement in order to use wherever i
want
to use the file name in the same workbook? I tried
something like public Myfilename , but it doesn't works?
Any help highly appreciated?


FlName = Format(Sheets("summary").Range("rptdate") -
1, "mmm dd")
Mydir = "C:\Daily Reports\"
If WeekDay(Sheets("summary").Range("rptdate")) =
vbMonday Then
myfile = Format(Sheets("summary").Range
("rptdate") -
3 - Range("holid"), mmm dd")
Else
myfile = Format(Sheets("summary").Range
("rptdate") -
Range("holid") - 1, "mmm dd")
End If
FileName = Mydir & "[" & myfile & "]"



.