John, your code also generated errors, but I found a solution which works,
although I'm not sure why. As I initially mentioned, my macro worked using
Excel 2000, but gave an error using Excel 2003, on the line:

Workbooks.Open Filename:="C:\Quality Assurance\Chemistry History\Monthly
Template.xls".

It turns out that the code works perfectly if I eliminate the ".xls" part of
the filename in that particular line. I would be interested, for reference,
if anyone can tell me why this happens. Thanks to all who helped, especially
John for taking the time to redo the code. Your assistance is greatly
appreciated.


"john" wrote in message
...
Dave Paterson has kindly highlighted what I was thinking of & suggested
test
hopefully will show any hard coding errors if they exist.
Other area of concern Dave rightly mentioned is that your VBA routine is
not
fully qualified which may be contributing to your problem.

Before I set off home I had a quick play with your routine - I think I
interpreted it correctly but if not, should give you some pointers how to
ensure that your code behaves as intended under expected operating
conditions. I also added error reporting to save the macro "crashing" out
if
things go wrong.

Sub SaveMonthly()

Dim strMYear As String
Dim strMMonth As String
Dim strMExist As String
Dim strMName As String
Dim FName As String
Dim MTwb As Workbook
Dim MCDws As Worksheet

FName = "C:\Quality Assurance\Chemistry History\Monthly Template.xls"

On Error GoTo myerror

Set MCDws = ThisWorkbook.Worksheets("Monthly Chemistry Data")

' -------------------------------
' Select Chemistry Data worksheet
' -------------------------------

Application.StatusBar = "SAVING MONTHLY REPORT... PLEASE WAIT"

' ------------------------------------
' Declare directory and file variables
' ------------------------------------

With MCDws

strMMonth = Month(.Range("A4").Value)

strMYear = Year(.Range("A4").Value)

End With

strMExist = Dir("C:\Quality Assurance\Chemistry Monthly Reports\" & _
strMYear & "\", vbDirectory)

If strMExist = "" Then

MkDir "C:\Quality Assurance\Chemistry Monthly Reports\" & strMYear
&
"\"

End If

'create fullfilename
strMName = "C:\Quality Assurance\Chemistry Monthly Reports\" & strMYear
& _
"\Chemistry Monthly Report " & strMMonth & "-" & strMYear &
".xls"
'
----------------------------------------------------------------------------------
' Copy Monthly Chemistry Data worksheet
' data to Monthly Template Workbook
'
----------------------------------------------------------------------------------
With MCDws

.Range("A10", .Range("M10").End(xlDown)).Copy

End With

'open the template
Set MTwb = Workbooks.Open(Filename:=FName)

'paste data to template
With MTwb

.Worksheets("Monthly Chemistry Data").Range("A10").Paste

' Save the new monthly report
.SaveCopyAs (strMName)

'close template
.Close False

End With


With Application

.CutCopyMode = False
.StatusBar = False

End With

' ----------------------------------------------------------
' Clear data from old monthly report
' ----------------------------------------------------------

With MCDws

.Range("A8").ClearContents
.Range("A10", .Range("M10").End(xlDown)).ClearContents

End With



' --------------------------------------------------

' --------------------------------------------------

answer = MsgBox("The Chemistry Monthly Report has been saved to the
file: " & Chr(10) & _
strMName, vbOKOnly)


myerror:
If Err 0 Then

Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description

MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext

Err.Clear

End If


End Sub





--
jb


"mcbarker" wrote:

The strange thing is that I use this routine in another macro in the same
workbook, which transfers data daily to an intermediate file, using an
identical method, and it has been working perfectly for years. I'll try
your
suggestion when I go back to work on Monday. Thanks.

"john" wrote in message
...
As a suggestion, why don't you try turning the macro recorder on then
open
the file on your network manually? You can then compare the recorded
result
with your hard coded version. This may, or may not, give you some
indication
why your version fails.
--
jb


"mcbarker" wrote:

The macro shown below works perfectly on my laptop using Excel 2000
(used
for writing and testing macros before placing them on the network).
When
I
transfer it over to the computer that it's actually going to be used
on,
which uses Excel 2003, the macro stops dead at the following line:

Workbooks.Open Filename:="C:\Quality Assurance\Chemistry
History\Monthly
Template.xls"

The error looks like this:
Run-Time error '1004'
'filename' could not be found. Check the spelling of the filename, and
verify that the file location is correct.
If you are trying to open the file from your list of recently used
files
on
the file menu, make sure that the file has not been renamed, moved, or
deleted.

The only difference between the two computer systems (apart from the
Excel
versions) is that the drive designation on the networked computer is
H:
instead of C:. All C: references are changed to H: on the working
computer.
All files are where they should be, and can be opened manually, or
found
doing a Windows search. I can't figure this one out. Any help would be
appreciated. Thanks.

***********************

Sub SaveMonthly()

Dim strMYear As String, strMMonth As String, strMExist As String,
strMName
As String

' -------------------------------
' Select Chemistry Data worksheet
' -------------------------------
Application.StatusBar = "SAVING MONTHLY REPORT... PLEASE WAIT"
Sheets("Chemistry Data").Select
' ------------------------------------
' Declare directory and file variables
' ------------------------------------
strMMonth = Month(Range("A4").Value)
strMYear = Year(Range("A4").Value)
strMExist = Dir("C:\Quality Assurance\Chemistry Monthly Reports\" &
strMYear
& "\", vbDirectory)
If strMExist = "" Then
MkDir "C:\Quality Assurance\Chemistry Monthly Reports\" & strMYear
&
"\"
End If
strMName = "C:\Quality Assurance\Chemistry Monthly Reports\" &
strMYear &
"\Chemistry Monthly Report " & strMMonth & "-" & strMYear & ".xls"
' ----------------------------------------------------------------------------------
' Select Monthly Chemistry Data worksheet and copy data to Monthly
Template
workbook
' ----------------------------------------------------------------------------------
Sheets("Monthly Chemistry Data").Select
Range("A10").Select
Range("A10", Range("M10").End(xlDown)).Select
Selection.Copy
Workbooks.Open Filename:="C:\Quality Assurance\Chemistry
History\Monthly
Template.xls"
Windows("Monthly Template.xls").Activate
Range("A10").Select
ActiveSheet.Paste
Range("A1").Select
ChDir "C:\Quality Assurance\Chemistry Monthly Reports\" & strMYear
ActiveWorkbook.SaveAs (strMName)
ActiveWorkbook.Close
ChDir "C:\Quality Assurance\Chemistry History\"
' ----------------------------------------------------------
' Clear data from old monthly report and template worksheets
' ----------------------------------------------------------
Range("A8").Select
Selection.ClearContents
Range("A10", Range("M10").End(xlDown)).Select
Selection.ClearContents
Workbooks.Open Filename:="C:\Quality Assurance\Chemistry
History\Monthly
Template.xls"
Windows("Monthly Template.xls").Activate
Range("A8").Select
Selection.ClearContents
Range("A10", Range("M10").End(xlDown)).Select
Selection.ClearContents
' --------------------------------------------------
' Save new monthly report and cleared template files
' --------------------------------------------------
ActiveWorkbook.Save
ActiveWorkbook.Close
Sheets("Chemistry Data").Select
Range("A1").Select
answer = MsgBox("The Chemistry Monthly Report has been saved to the
file:
"
& strMName, vbOKOnly)
Application.StatusBar = False

End Sub




__________ Information from ESET NOD32 Antivirus, version of virus
signature database 3970 (20090327) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com






__________ Information from ESET NOD32 Antivirus, version of virus
signature database 3970 (20090327) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com






__________ Information from ESET NOD32 Antivirus, version of virus
signature database 3970 (20090327) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com






__________ Information from ESET NOD32 Antivirus, version of virus signature database 3981 (20090401) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com