Macro runtime 1004 error on opening worksheet

[Shaggyjh]

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]

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]

Hi Dave,

Thank you very much for your code. It works brilliantly if i add a button.
Is there anyway of making it work automatically when the Menu worksheet is
opened. I then don't have to worry about users having to press the button!

Would i just swap the code:

Option Explicit
Sub CommandButton1_Click()

with the code below on the Menu sheet?

Private Sub Worksheet_Activate()

Thanks for your help so far.

 

[Dave Peterson]

Put the code under the Menu worksheet module.

Rename it to
Private Sub Worksheet_Activate()
not:
Sub CommandButton1_Click()

This will run when you activate the Menu worksheet.

 

[Shaggyjh]

The code is great. Thanks.

Is there anyway to change it so that if there is only one choice then it
just shows that one choice not an error message?

I have altered the code to: -

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

Is that ok?

Cheers,

James

 

[Dave Peterson]

The macro expects the data to have a header row.

The code checks to see if the only row visible after applying the autofilter is
that header row.

I guess I don't understand what should happen if there are no visible data rows.

Maybe...

The code is great. Thanks.

Is there anyway to change it so that if there is only one choice then it
just shows that one choice not an error message?

I have altered the code to: -

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

Is that ok?

Cheers,

James