Solved: Saving worksheets as separate workbooks

[Keith W]

Hi,
New member, fairly new with VBA, but would appreciate some help on the following:
I have a workbook that contains student reports, up to 50 worksheets are allowed, but not all used.
Some students work with different employers, and what i would like some help with is, does anyone know a way of saving each worksheet as a separate workbook with the title of the worksheet (which is the students name) in a folder which has a name specific to the employers. The students worksheets each have a cell with the employer saved in a cell.
I have managed to be able to write some code which will Identify the employer from the student worksheets and use this as part of a path name to the relevant employer folder, but I dont seem to be able to crack a way of splitting the worksheets into separate workbooks. I have attached a sample of code as to how far I have got so far, but now I need some help to put code where shown.
Incidentally the new workbooks will not have to use any active links after saving although the originals actually do.
Also I need to keep the sheet formatting in the new workbooks.
Hope this is not too much to ask first time up ! Any help would be much appreciated.
Thanks in anticipation.

[Jacob Hilderbrand]

See if this gets you started.

[VBA]
dim ws As Worksheet

For Each ws In Worksheets
ws.Copy
ActiveWorkbook.SaveAs Filename:=(Your path and file name here)
ActiveWorkbook.Close
Next

[/VBA]

[lucas]

Hi Keith, do your employer folders definatly already exist?

ps you can include your code in your post. When posting code, select the code and hit the vba button and it will be formatted for the forum.

Keith's code from his doc file:
[VBA]Sub MakeMultipleXLSfromWB()
Dim CurWkbook As Workbook
Dim wkSheetName As String
Dim Emp As String
Dim xpathname As String, dtimestamp As String
dtimestamp = Format(Now, "ddd dd mmm yyyy hh:mm:ss")
For i = 1 To Sheets.Count
Application.ScreenUpdating = False
Sheets(i).Activate
Set CurWkbook = Application.ActiveWorkbook
Emp = Cells(4, 8)
If Emp = " " Then GoTo BlankSheet
xpathname = "J:\FOT_All\NAT cert Z7807\Admin\Emp\dtimestamp"
'NEED HELP WITH CODE HERE !!
BlankSheet:
Next i
End Sub[/VBA]

[Keith W]

Hi Lucas,
Thanks for the tip I will try that next time !
In answer to your question, the employer folders do exist and they have exactly the same title as exists on the student worksheets.
I did quickly add DRJs' code but it gives me a run time error 91 with a statement 'Object variable or With block variable not set' I am not using a with block so i assume its the former, but I cant fathom it at the moment
Thanks again for the tip.