Macro runtime 1004 error on opening worksheet
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...
If VisRng Is Nothing Then
Destcell.value = "No non-blanks in AA column A"
Else
VisRng.Copy _
Destination:=DestCell
End If
Shaggyjh wrote:
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" wrote:
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 wrote:
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" wrote:
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
--
Dave Peterson
--
Dave Peterson
|