problems saving

S

stubrace

I have a spreadsheet which is used as a template. Data is filled i
then a macro is run to copy data to an accumulation spreadsheet an
create charts etc. The macro then saves a copy of the template using
date range in a field on the template spreadsheet, creating a director
for the particular calendar month.
Using code gleaned from you knowledgable people I have managed to ge
it all to work and check for existence of directories etc whils
saving.
For me it works right first time every time, however, when one of m
staff does the work and runs the macro, it appears to run correctly an
in fact does all the copying to the accumulation sheet and appears to d
the saving of the template file ( the name changes in the top statu
bar). When you then go to the directory which supposedly contains th
saved copy, it is not there.

The member of staff can quite happily create files and save them to th
directory manually, so I am assuming there are no permissions issue
with access to the directory etc.

Can anyone explain why this is happening, is it an excel problem or
windows problem.
The version of excel is Excel 2002 SP2 running on XP pro

The code follows, which as I said works for me but not for m
colleague, many thanks in advance:

Option Explicit

Sub DoTheBiz()
'
' DoTheBiz Macro
'

'
Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Workbooks("Kestrel trend
overlaid.xls").Sheets(5 _
)
Sheets("Humidity").Select
ActiveChart.SeriesCollection.Ad
Source:=Sheets("Sheet1").Range("D2:D42"), _
Rowcol:=xlColumns, SeriesLabels:=False, _
CategoryLabels:=False, Replace:=False
Sheets("Temperature").Select
ActiveChart.SeriesCollection.Ad
Source:=Sheets("Sheet1").Range("C2:C42"), _
Rowcol:=xlColumns, SeriesLabels:=False, _
CategoryLabels:=False, Replace:=False
Sheets("Wind speed").Select
ActiveChart.SeriesCollection.Ad
Source:=Sheets("Sheet1").Range("B2:B42"), _
Rowcol:=xlColumns, SeriesLabels:=False, _
CategoryLabels:=False, Replace:=False
Sheets("All data").Select
Sheets("Sheet1").Select
Range("A1").Select
ActiveWindow.LargeScroll Down:=1
Range("A1:D42").Select
Selection.Copy
Sheets("All data").Select
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns
SeriesLabels:=True, _
CategoryLabels:=True, Replace:=False, NewSeries:=True
Sheets("Sheet1").Select
Range("D42").Select
ActiveWindow.LargeScroll Down:=-1
Range("A1").Select
ActiveWindow.LargeScroll Down:=1
Range("A1:D42").Select
Application.CutCopyMode = False
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D42")
_
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 1").IncrementLeft 102#
ActiveSheet.Shapes("Chart 1").IncrementTop -90.75
Windows("Kestrel trends overlaid.xls").Activate
Range("B48").Select
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Name = Format(Range("b48"), "dd mmmm yyyy")
Windows("Kestrel Plots template.xls").Activate
On Error Resume Next
MkDir ("O:\computer room management\New Genera
Folder\Administration\Sherwood Specific\Kestrel plots\"
Format(Range("b48"), "mmmm yyyy"))
On Error GoTo 0
ChDir _
"O:\computer room management\New Genera
Folder\Administration\Sherwood Specific\Kestrel plots\"
Format(Range("b48"), "mmmm yyyy")
ActiveWorkbook.SaveAs Filename:= _
"Kestrel plots" & Format(Range("b48"), " ddmmyyyy") & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=True, CreateBackup:=False
End Su
 
Top