Hi there,
I'v pieced together a macro that will split two worksheets out from a master. Both of these files are then saved in the same location as the master file.
The problem is that the macro I have saves the files to the C drive (not where the master is saved). I can see why this is but don't know how to change it.
Sub SpittingFiles()
'
' SpittingFiles Macro
' Macro recorded 27/05/2010 by RS
' Move worksheets 'Error Report' and 'Journal' into separate files and save in the same
' location as 'M2 Forecast Outturn'
'
Sheets("Error Report").Select
Sheets("Error Report").Move
Call Save
Windows("M2 Forecast Outturn.xls").Activate
Sheets("Journal").Select
Sheets("Journal").Move
' This part of the code shows the recorders correct path but will be replaced by 'Save'
' when correct
ActiveWorkbook.SaveAs FileName:= _
"C:\Documents and Settings\RSpring\My Documents\M2\Journal.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Windows("M2 Forecast Outturn.xls").Activate
Range("A1").Select
End Sub
Sub Save()
' Save file to the same path as the active workbook
MName = ActiveSheet.Name & ".xls"
MDir = ActiveWorkbook.path
ActiveWorkbook.SaveAs FileName:=MDir & "\" & MName
End Sub
Can anyone help?
Thanks
Bobbi
Bookmarks