Macro Reset error

[rushdenx1]

The enclosed file 'Performance Data Dump - old' contains a months worth of data which is added to on a daily basis and sorted and filtered into the 'Lettered' tabs, using Macro 'FormatFilterSortAndPasteToSheet'. I now want to start a new month, so thought the following would work. I saved the existing spreadsheet as a new file Performance Data Dump - 1 day. (also enclosed). I run the Macro 'ClearSheetsNotData' which empties the data under the 'Lettered' tabs. I deleted all the data under the 'Data' tab. I have now populated the first days worth of information into the 'Data' tab. The problem is when I now run the Macro 'FormatFilterSortAndPasteToSheet' it stops with an error. Can someone please let me know what I've done wrong. Thanks Kevin (in the UK using Excel 2002 SP3)

[foxguy]

Hi Kevin;

When I wrote the macro, I failed to take into account that there might be no data in sheet("B"). When there is not Data in "B" then iLastColumn remains 0 and crashes the .AutoFit line.
So find this section

Please Login or Register  to view this content.

add the line that clears the sheet (this will make it unnecessary to run "ClearSheetsNotData" every month)
also add the test to see if there is no data.

Please Login or Register  to view this content.

[rushdenx1]

Foxguy, thanks for your quick response. I have added the line to the new file enclosed. However when I run the Macro 'FormatFilterSortAndPasteToSheet'. It still Stops. When I close down the debugger, the DATA sheet is now grey apart from the headings. The original data has disappeared and you cannot add anything due to the 'grey'. Kevin.

[foxguy]

Hi Kevin;

Sorry about that, I said I didn't have time. I guess I should have waited until I had the time to test it.
Here's a file with better error trapping, so if an error occurs again it won't leave your "Data" still filtered (that's why it was gray). Copy of Performance Data Dump Ver 2.zip
Had to zip it because it was too big.

You might try reducing the size of the file by:

Please Login or Register  to view this content.

This should reduce the size of your file (hopefully)