Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro runtime 1004 error on opening worksheet
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro runtime 1004 error on opening worksheet
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro runtime 1004 error on opening worksheet
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro runtime 1004 error on opening worksheet
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro runtime 1004 error on opening worksheet
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unable to protect cells in macro sheet b/c runtime error 1004 | Excel Worksheet Functions | |||
Macro runtime error 1004 with Autofilter | Excel Discussion (Misc queries) | |||
Another runtime 1004 error | Excel Discussion (Misc queries) | |||
Runtime Error '1004' | Excel Discussion (Misc queries) | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) |