Excel VB 2000 not working in 2003
 

I've seen a previous post, but could not apply it to my case.
A macro which works well in Excel 2000 is not working in 2003, which follows;

Sub SaveFile()
Sheets("Journal").Select
Range("E3").Select
Selection.End(xlDown).Select
Name = Selection
Sheets("Worksheet").Select
Sheets("Worksheet").Copy
ChDir "C:\CLAU\Accounting"
ActiveWorkbook.SaveAs FileName:=Name

ActiveWindow.Close
Range("E11").Select

End Sub

This may not be the most kosher method of saving a worksheet, but it used to
work. I am trying to automaticallys save a worksheet which is named according
to a cell name in the file. (Line 1 to line 3 are to land on the correct cell
which I want to use as my file name, which is a concatenate of several
seperate cells) Although this macro used to work well in <2003, it stops now
on Name = Selection (specifically on Name) and returns error "Compile Error:
can't find project or library". Shoud I Dim Name as something?
PLEASE HELP!
Thanks,

John Manship
Siemens COM Br.
(55-11) 3908-2989

Hi Lost,

Name is a reserved keyword in Excel VBA. And, yes, it is a good idea to
declare variables.
Change to something like this;

Sub SaveFile()
Dim strName As String

strName = Sheets("Journal").Range("E3").End(xlDown).Value

Sheets("Worksheet").Copy
ChDir "C:\CLAU\Accounting"
ActiveWorkbook.SaveAs Filename:=strName

ActiveWindow.Close
Range("E11").Select

End Sub

Ed Ferrero


"A Lost Soul" <A Lost wrote in message
...
I've seen a previous post, but could not apply it to my case.
A macro which works well in Excel 2000 is not working in 2003, which
follows;

Sub SaveFile()
Sheets("Journal").Select
Range("E3").Select
Selection.End(xlDown).Select
Name = Selection
Sheets("Worksheet").Select
Sheets("Worksheet").Copy
ChDir "C:\CLAU\Accounting"
ActiveWorkbook.SaveAs FileName:=Name

ActiveWindow.Close
Range("E11").Select

End Sub

This may not be the most kosher method of saving a worksheet, but it used
to
work. I am trying to automaticallys save a worksheet which is named
according
to a cell name in the file. (Line 1 to line 3 are to land on the correct
cell
which I want to use as my file name, which is a concatenate of several
seperate cells) Although this macro used to work well in <2003, it stops
now
on Name = Selection (specifically on Name) and returns error "Compile
Error:
can't find project or library". Shoud I Dim Name as something?
PLEASE HELP!
Thanks,

John Manship
Siemens COM Br.
(55-11) 3908-2989

Thanks Ed, You made my day!! That did the trick. Thanks again for the help.
Best regards,

John Manship

"Ed Ferrero" wrote:

Hi Lost,

Name is a reserved keyword in Excel VBA. And, yes, it is a good idea to
declare variables.
Change to something like this;

Sub SaveFile()
Dim strName As String

strName = Sheets("Journal").Range("E3").End(xlDown).Value

Sheets("Worksheet").Copy
ChDir "C:\CLAU\Accounting"
ActiveWorkbook.SaveAs Filename:=strName

ActiveWindow.Close
Range("E11").Select

End Sub

Ed Ferrero


"A Lost Soul" <A Lost wrote in message
...
I've seen a previous post, but could not apply it to my case.
A macro which works well in Excel 2000 is not working in 2003, which
follows;

Sub SaveFile()
Sheets("Journal").Select
Range("E3").Select
Selection.End(xlDown).Select
Name = Selection
Sheets("Worksheet").Select
Sheets("Worksheet").Copy
ChDir "C:\CLAU\Accounting"
ActiveWorkbook.SaveAs FileName:=Name

ActiveWindow.Close
Range("E11").Select

End Sub

This may not be the most kosher method of saving a worksheet, but it used
to
work. I am trying to automaticallys save a worksheet which is named
according
to a cell name in the file. (Line 1 to line 3 are to land on the correct
cell
which I want to use as my file name, which is a concatenate of several
seperate cells) Although this macro used to work well in <2003, it stops
now
on Name = Selection (specifically on Name) and returns error "Compile
Error:
can't find project or library". Shoud I Dim Name as something?
PLEASE HELP!
Thanks,

John Manship
Siemens COM Br.
(55-11) 3908-2989