If the code is in a general module, those unqualified ranges will refer to the
active sheet.

If the code is in a worksheet module, then the unqualified ranges will refer to
the sheet that owns the code. And you can only select a range on the
activesheet.

If the sheet with the code is named Sheet1, it's the equivalent of:

Sheets("AA").Select
worksheets("Sheet1")."Range("A1:O100").Select

And that won't work.

The good news is that you can do the work without selecting worksheets or
ranges.

This copies the list of non-blank entries in column A of Sheet AA to Sheet2 cell
A1. It avoids the header in row 1 of Sheet AA, too.

I didn't know how you determined where to put the data, so I used A1.

Option Explicit
Sub CommandButton1_Click()

Dim VisRng As Range
Dim DestCell As Range

With Worksheets("Sheet2")
'I don't know how you determine where to paste the data
Set DestCell = .Range("A1")
'erase any existing values in this column
DestCell.EntireColumn.ClearContents
End With

With Worksheets("AA")
.AutoFilterMode = False
.Columns(1).AutoFilter Field:=1, Criteria1:="<"

With .AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then
'nothing visible
Set VisRng = Nothing
Else
'resize to avoid the header
'and come down one row
Set VisRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End If
End With
End With

If VisRng Is Nothing Then
MsgBox "No non-blanks in AA column A"
Else
VisRng.Copy _
Destination:=DestCell
End If

End Sub

I added a commandbutton to the sheet and used its _Click event to run the macro.

And because there is no selecting, I didn't have to make those worksheets
visible.


Shaggyjh wrote:

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

--

Dave Peterson