The other day, someone sent me a spreadsheet with multiple worksheets. I needed to be able to send each worksheet to an individual for review, and each recipient did not need to see everyone else's sheet. So I found a tip on-line that included the following VBA code to save the sheets to separate Excel files. Life was good until those who didn't have Office 2007 could not open their sheets, even though the original file had been saved with the Excel 97-2003 option. They had first received the message, "The file you are trying to open, "<filename>" is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?" When they said yes, all they saw was a scramble of machine code. I thought that maybe there was a special extension for Excel 97-2003 files, but when I looked it up in a book I have, it just said the extension was .xls, which is what the code used. Is there other formating that needed to be saved? Any help would be appreciated. Here's the code.
[Code]
Sub Copy_Sheets_to_Separate_Workbooks()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = True Then
ws.Activate
ActiveSheet.Copy
With ActiveWorkbook
.SaveAs "C:\Documents and Settings\bruce.vanbibber\My Documents\TITLE I - M A I N\Title I Budgets\Allocation Budgets - Schoolwide - FY 10\" & _
InputBox("Please enter the Save As Name...", "Worksheet Save As") & ".xls"
.Close
End With
End If
Next ws
End Sub
[Code]
Bookmarks