RE: MACRO to save worksheets as seperate files ?

[stelmarkov]

RE: MACRO to save worksheets as seperate files ?

Hello, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I've found in your archive the Macro code Posted by Dank on November 14, 2001 12:51 AM re how to Saves worksheets as separate files from one main workbook<o></o>
<o></o>
This code does save the worksheets as a separate files and that is exactly what I’ve been looking for however <o></o>
<o></o>
1) I don't need to save each worksheet from my master work book e.g I need to save only 20 separate files
<o> </o>
How do I amend the below code posted by Dank ? Can we specify in the code the range of the worksheets I need to save ? <o></o>
<o> </o>
Please note that I am a beginner and I found the below code a bit simple and understanding so is it possible to amend the below code to my need
PS. The Worksheets that I need to copy and distribute are named with different Doctors Surnames<o></o>
<o> </o>
2) I work in Excel 2007 but most of my master workbooks are saved in .xls and not in .xlsx as we send the files to external users and not every user have latest version of excel, Dank’s macro currently saves all files in .xlsx<o></o>
<o> </o>
I’ve tried to amend the following Save As macro to <o></o>
wbDest.SaveAs strSavePath & sht.Name & " " & Format(DateSerial(Year(Now), Month(Now) - 1, 1), "mmmm yy") & ".xls")
<o> </o>
The Marco saves the file with ext .xls BUT when I open the file, message says that” The file is saved in different format than specified in the file extension” Where in the macro can you specify the extension of the file then?<o></o>
<o> </o>
<o></o>I also tried to amend / add the following But I am getting the message “Object Variable or with block variable not set”<o></o>
<o> </o>
Dim strFile As String<o></o>
strFile = Dir(strSavePath & sht.Name & " " & Format(DateSerial(Year(Now), Month(Now) - 1, 1), "mmmm yy") & ".xls")<o></o>
wbDest.SaveAs strFile<o></o>
<o> </o>
<o>Thanking you in advance</o>
<o>Kind Regards,</o>
<o>Stal</o>
<o> </o>
Please refer to the Macro posted by Dank <o></o>
Sub CreateWorkbooks()
'Creates an individual workbook for each worksheet in the active workbook.
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sht As Object 'Could be chart, worksheet, Excel 4.0 macro,etc.
Dim strSavePath As String <o></o>
<o> </o>
On Error GoTo ErrorHandler<o></o>
<o></o>
Application.ScreenUpdating = False 'Don't show any screen movement <o></o>
strSavePath = "C:\Temp\" 'Change this to suit your needs <o></o>
<o> </o>
Set wbSource = ActiveWorkbook <o></o>
For Each sht In wbSource.Sheets
sht.Copy
Set wbDest = ActiveWorkbook
wbDest.SaveAs strSavePath & sht.Name
wbDest.Close 'Remove this if you don't want each book closed after saving.
Next <o></o>
Application.ScreenUpdating = True <o></o>
Exit Sub <o></o>
ErrorHandler: 'Just in case something hideous happens
MsgBox "An error has occurred. Error number=" & Err.Number & ". Error description=" & Err.Description & "."
End Sub

 

[markmzz]

Re: MACRO to save worksheets as seperate files ?

Mark - thank you for your help! It work however the path that the workbooks were saved into are different from the folder that the original file was in. The macro saved the workbooks into:

C:\Documents and Settings\matt\Application Data\Microsoft\Excel\XLSTART

Could the fact that im on a company computer be affecting the path where excel defaults to?

thanks again!

Nikegeo,


Help of Excel

Application the ThisWorkbook property

Returns a Workbook object that represents the workbook where the current macro code is running.

Use this property to refer to the workbook that contains the macro code. ThisWorkbook is the only way to refer to an add-in workbook from inside the add-in.

Workbook Path property

Returns a String value that represents the full path to the application, excluding the final separator and name of the application.

Make sure that the macro is actually stored in the master workbook.

The following command sets the location where Excel must save the new workbook (same path of the master workbook - were is the macro)

'Store the path of the master workbook
'in to the variable strSavePath
strSavePath = ThisWorkbook.Path & "\"

Markmzz

 

[nikegeo]

Re: MACRO to save worksheets as seperate files ?

Mark - thanks for the quick reply - I saved the Macro in the workbook, not the personal, and it works perfectly!

Thank You so much for all your help

-Matt

 

[markmzz]

Re: MACRO to save worksheets as seperate files ?

Mark - thanks for the quick reply - I saved the Macro in the workbook, not the personal, and it works perfectly!

Thank You so much for all your help

-Matt

Matt,

You are welcome and thanks for the feedback.

Markmzz