On Tuesday, February 18, 2014 9:02:44 PM UTC-6, JeanPierre Charron wrote:
My Excel VBA code parse a worksheet to 3 workbooks and works without error.

However, when I try the generated workbooks I get the following message :

.

The file you are trying to open, 'C.xls' is in a different format than specified by the file extension.

Verify than the file is not corrupted and is from a trusted source before opening the file.

Do you want to open the file now ?

If I click 'Yes' the workbook opens normally with correct content.

How do I avoid the preceding annoying message ?

Help appreciated.

.

The corresponding VBA code follows :

.

Sub x()

Dim r As Long, rng As Range, ws As Worksheet

Application.DisplayAlerts = False

Application.ScreenUpdating = False

With Sheets("Data")

Sheets.Add().Name = "Test"

.Range("A1", .Range("A" & Rows.Count).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Test").Range("A1"), Unique:=True

For Each rng In Sheets("Test").Range("A2", Sheets("Test").Range("A2").End(xlDown))

.AutoFilterMode = False

.Range("A1").AutoFilter field:=1, Criteria1:=rng

Set ws = Sheets.Add

.AutoFilter.Range.Copy ws.Range("A1")

ws.Name = rng

ws.Move

ActiveWorkbook.Close SaveChanges:=True, Filename:="C:\2013\PM4\" & rng & ".xls"

Next rng

.AutoFilterMode = False

Sheets("Test").Delete

End With

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub
This may help to either save each sheet after making all or to see how to modify your code

Set wsbSource = ActiveWorkbook
For Each sht In wbSource.Sheets
sht.Copy
Set wbDest = ActiveWorkbook
wbDest.SaveAs strSavePath & sht.Name
wbDest.Close
Next
or one from Ozgrid
Sub ExportToWorkbooks()
Dim NewBook As Workbook, OldBook As Workbook, sh As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Set OldBook = ThisWorkbook

For Each sh In OldBook.Worksheets
If sh.Visible = True Then
sh.Copy
ActiveWorkbook.SaveAs Filename:=OldBook.Path & "\" & sh.Name & "VALUES", FileFormat:=xlWorkbookNormal
ActiveWorkbook.Close
End If
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub