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