Vba error 400 in macro using excel 2007

**PeterJohns** · 02-24-2014, 07:43 AM

I have a macro for splitting worksheets to form separate list of data dependant of what column is specified.
This has worked perfectly well in 2003 and 2007 worksheets.
However I recently had to split a worksheet that had originally been an xml file which I had imported into excel. Apart from a couple of minor formmatting issues, all the data imported successfully. Upon running the macro, it keeps stopping with an error 400 message box.
Upon stepping through the macro I found that the error occured when it came to the following line of code

If .FilterMode Then .ShowAllData

It filtered correctly but when it came to ShowAllData, the sheet remained blank and kicked in the Error 400.
I've set the AutoFilterMode to false earlier in the macro.

Any ideas would be most helpful

**HaHoBe** · 02-24-2014, 01:17 PM

Hi, PeterJohns,

you might like to consider to wrap teh code line with code.atgs.

Without seeing all of your code or an example I would try to use

Please Login or Register  to view this content.

which would reset the Autofilter to none or use the same criteria columns again to get the information to be displayed again.

Ciao,
Holger

**PeterJohns** · 02-25-2014, 05:22 AM

Hi Holger, If only life was that simple.
I tried running the macro by selecting another column to 'sort' by, this ran perfectly.
This would suggest that the format for the column I need to sort by is the problem.
I've so far tried changing the format from Text to General to Number, to no effect
The column contains a specific code in the form 01-01, 01-02 etc upto 70-01, then there are some alphanumeric but at present i have left them out of the test.

**HaHoBe** · 02-25-2014, 03:08 PM

Hi, PeterJohns,

could you attach a sample workbook with some data and maybe a bit more of the code that causes the error as I´m pretty convinced that rebuilding a workbook for test won´t show that behaviour.

Ciao,
Holger

**PeterJohns** · 02-26-2014, 11:44 AM

Holger,
Here's the section of the code
With ActiveWorkbook

For x = 1 To ContList.Count

If ContList(x) Like "*Dean *" Then
Debug.Print "wait!"
End If

.Sheets(1).Copy After:=.Sheets(1)
With .Sheets(2)
.Name = Left(ContList(x), 31)
.Cells(lastRow, SplitColNum).AutoFilter field:=SplitColNum, Criteria1:="<>" & ContList(x)
.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Delete
If .FilterMode Then .ShowAllData
.[a65536].End(xlUp).Select
End With