Declaring Public Procedure

  • Thread starter Thread starter mac
  • Start date Start date
M

mac

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?


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 & "]"
Click to expand...


.
Click to expand...
 
Try

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

--
Regards,
Tom Ogilvy

mac said:
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?


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 & "]"
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top