Solved: AutoFilter macro issues

[OhGorgeous1]

Hi all

I would be most grateful if anyone can help me.

I have a very large table (below is a very small sample) and I have a macro that autofilters on the Area and then copies the results into a new sheet. Works wonderfully.

You may ask then what is the problem....well

The problem is that when you do a search for 'Bristol' it doesn't work but does for 'Wiltshire'

I have tried renaming all occurances of Bristol within the Area Column but this still doesn't work.

It is now driving me nuts :banghead: so any help would be a lifesaver.


I have attached an example of data but the code is below and not within the sheet (the actual workbook is too big and sensitive, sorry)


Code:


Sub AutoFilter_CopyPaste19()
'turn off screen update, allow hidden sheets to be visible
Application.ScreenUpdating = False
Sheets("AllData").Visible = True
Sheets("Data").Visible = True
Sheets("DataTemplate").Visible = True
'clear down and prepare AllData sheets
Sheets("AllData").Select
Cells.Select
Selection.ClearContents
Sheets("AllData").Select
Columns("A:Y").Select
Selection.ClearContents
Sheets("DataTemplate").Select
Rows("1:1").Select
Selection.Copy
Sheets("AllData").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
CurrentFileName = ActiveWorkbook.Name
'activate data sheet
Sheets("Data").Activate
'select rows
Rows("1:1048576").Select
' run auto filter dependent on the criteria entered onto the menu page
Selection.AutoFilter Field:=9, Criteria1:=Worksheets("Menu").Range("G19").Value
'copy filtered data
Selection.Copy
'activate AllData sheet
Sheets("AllData").Select
'paste values into AllData sheet
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
'clear selection
Range("A1").Select
'run the macro to update the head count
Run "FillDown"
'turn on screen update and rehide sheets
Application.ScreenUpdating = True
Sheets("AllData").Visible = False
Sheets("Data").Visible = False
Sheets("DataTemplate").Visible = False
'message box to inform user the job is complete
MsgBox "Your Search Is Complete.", 64, "Search Completed"
'activate data sheet
Sheets("Data").Activate
' remove auto filter
Selection.AutoFilter
' return to the menu sheet
Sheets("Menu").Select
End Sub

[mdmackillop]

Your code refers to sheets that are not in your workbook. Please provide a working sample.

[OhGorgeous1]

All sorted now, thanks anyway

[mdmackillop]

Can you please post your solution for the benefit of others?