Thread: 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

Try this

ChDir "C:\CLAU\Accounting"
Sheets("Journal").select
ActiveWorkbook.SaveAs FileName:=Selection.Range("E3").End(xlDown).value
Sheets("Worksheet").Select
ActiveWindow.Close

Philippe

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 Soul@discussions.microsoft.com> wrote in message
news:262334FF-E806-4356-9470-546AB808F6B1@microsoft.com...
> 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 Soul@discussions.microsoft.com> wrote in message
> news:262334FF-E806-4356-9470-546AB808F6B1@microsoft.com...
> > 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
>
>
>