I have a macro (coding below) that autofilters a worksheet (AA) then removes
blanks, copies the data then pastes it on a seperate sheet (sheet2). This is
then used for a data validation list.
A long way round to get only nonblank values in a data validation list on a
seperate worksheet called Menu.

The code:

Sub AA()
'
' AA Macro
' Macro recorded 01/05/2009 by Shaggyjh
'

'
Sheets("AA").Visible = True
Sheets("Sheet2").Visible = True
Sheets("AA").Select
Range("A1:O100").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<"
Range("A1:A101").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("AA").Visible = False
Sheets("Sheet2").Visible = False

End Sub

If i run this macro via the tools menu it works perfectly. However if i
open the view code on the Menu worksheet and enter the following code i get
an error!
The error is highlighting:

Range("A1:O100").Select

What am i doing wrong? Or is there a better way to do it? I want it to
automatically refresh the data validation list on the hidden sheet2 so that i
don't have to rely on users to click a button before using the drop down list!



Private Sub Worksheet_Activate()

'
' AA Macro
' Macro recorded 01/05/2009 by Shaggyjh
'

'
Sheets("AA").Visible = True
Sheets("Sheet2").Visible = True
Sheets("AA").Select
Range("A1:O100").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<"
Range("A1:A101").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("AA").Visible = False
Sheets("Sheet2").Visible = False

End Sub