Macro - File saving issue

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hi,

Would appreciate any help on the following :-

I have a macro which opens 4 seperate spreadsheets and formats them.
The files are call ocean.xls, air.xls etc. I wanted the macro to also
rename and save the files automatically adding the date at the end
i.e. it would now be called ocean 01012004.xls etc

Not knowing how to do this I searched the groups and used the
following code:-

Range("A2").Select
Dim str As String
str = Trim(VBA.Format(Now(), "DDMMYYYY"))
Debug.Print str
ActiveWorkbook.SaveAs Filename:="Y:\GSK Administration\Excel\GSK Bond
Reports\GSK Bond Reports 2004\Ocean_2004\Ocean " & str

This works and saves my file which is fine, the only problem is how do
I automatically get excel to close the original file ocean.xls without
saving it as I no longer need it. As there are 4 reports I end up
with 8 sheets open, and have to keep doing windows, select the
relevant sheet, close, select no (not to save changes) etc.

I tried adding:-

Windows("ocean.XLS").Activate
ActiveWindow.Close

directly to the bottom of the above macro but it comes up with
run-time error 9 subscript out of range error.

Any ideas - many thanks.
 
Dave,

I don't get this problem. If I save an open workbook under another name, I
only have one workbook left, not two, and that is the renamed version.

Try it in Excel and see what you get. There may be more code that is the
real culprit.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top