Macro

[Justin]

Hi there,
I am trying to write a macro to 'Save As'

I have a workbook that rolls over each financial year and
I would like to set up a macro for it to re-name it's self
to *2003, *2004, *2005 etc.

Can this be done?

Thanks for your help?
Justin

 

[curllion]

I am chinese ,I am sorry that i am not good at English
'---This macro can save each sheet of the workbook as a single workbook that
name is that sheet's name.------
Sub breakworkbook()
Dim w As Workbook
Set w = ActiveWorkbook
For Each i In w.Sheets
j = i.Name
On Error GoTo Loop1
i.Move '---------------------------------------------or "i.copy"
ActiveWorkbook.SaveAs "C:\temp\" & j & ".xls"
Workbooks(j & ".xls").Close savechanges:=False
Next
Loop1:
j = w.Sheets(1).Name
w.SaveAs "C:\temp\" & j & ".xls"
MsgBox "Mission Accomplished,and they are be saved in " & Chr(34) &
"C:\temp" & Chr(34)
End Sub

'---This macro can save each sheet of the workbook as a single workbook that
name is *2001, *2002, *2003 etc.------
Sub breakworkbook1()
Dim w As Workbook
k=1
Set w = ActiveWorkbook
For Each i In w.Sheets
if k<10 then
k1="0" & Cstr(k)
else
k1= Cstr(k)
end if
j = i.Name
On Error GoTo Loop1
i.Move '----------------------------------------------------or "i.copy"
ActiveWorkbook.SaveAs "C:\temp\" & "20" & k1 & ".xls"
Workbooks(j & ".xls").Close savechanges:=False
k=k+1
Next
Loop1:
j = w.Sheets(1).Name
w.SaveAs "C:\temp\" & j & ".xls"
MsgBox "Mission Accomplished,and they are be saved in " & Chr(34) &
"C:\temp" & Chr(34)
End Sub

 

[Justin Young]

Thanks for that, I will give it a try.

Cheers.
Justin.