Printable View
Good morning.
I have a ? regarding a run time error (1004) in VB for Excel. I have a macro that runs fine through Alt F8. I created a control button and attached the macro. When I use the control button I get run time error 1004 with the message that says "Select method of range class failed." I am new at VB and any suggestions would be greatly appreciated. Thanks in advance for your assistance.
:)
-Helene
I don't know if this is any help, but I had a similar problem and this is the note I made to myself relative to that:
Note, doing a WorkBooks…Add after a .Copy apparently clears out the paste buffer,
i.e. the copied data is lost, so attempting a PasteSpecial fails since the buffer is now empty.
Solution: do the .Copy after the .Add
When doing a .Copy / .PasteSpecial, avoid doing anything in between.
Thank you for your help Dave.
I'll le tyou know how it works out.
Have a great day.
-Helene:wave:
Dave -
I sent your suggestions down to the tech dept, and they couldn't help me. I sort of understand what you're saying, however I don't have any .Add in the macro. When I debug, it highlights Range("A10:D1000").Select
No one here seems to be able to help and I'm stuck. Any other suggestions? Thanks again.
Enjoy the day.
-Helene:wave:
I don't know why the smile comes up in the middle of the range sorry, it's a D.
Post the macro here (or if it's big, just a section up to that point), and we should be able to see whats wrong. :)
Good afternoon.
Thanks in advance for your help si. (I'm an analyst, not a programmer.) And, I'm the so called Excel whiz, so I guess it's time to learn to play with the big guys.
Okay here goes. (This is only part of it, when printed its 4 1/2 pages.) The runtime error occurs immediately. Line 2.
Private Sub CommandButton1_Click()
'
Sheets("2-04 Up").Select
Range("A10:D1000").Select
Selection.Copy
Sheets("Run ").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("3-04 Up").Select
Range("A10:D1000").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Run ").Select
ActiveWindow.LargeScroll Down:=34
Range("A1001").Select
ActiveSheet.Paste
Like I mentioned, the macro runs on its own, it just has problems when I try and attach it to a command button.
Thank you so much.
-Helene:wave:
Well the good news is I can guess what the problem is - basically the sheet hasn't got the focus.
I'm not entirey sure what code you need, but I think this will do it:
If not, try adding this at the start:VB Code:
:)VB Code:
Hey Si -
What do you mean the sheet doesn't have a focus?
Since you're the guru and I'm the novice, I'll just do what you say, and take your word for it. I'll let you know how it goes, and thanks again for your help.
Have a great day.
-Helene:wave:
Hey Si -
The .Activate didn't work. Still get the same error on the same line.
Tried the .Workbook.Activate
Gives me runtime error 438
Object doesn't support this property or method
Debug stops at .workbook.activate
I don't understand why the macro works by itself then?
Totally baffled!
-Helene
:confused:
Thought I'd try some alternatives.
Tried Run([Macro],[arg1],[arg2],.....
That produces a syntax error
And ...Click(name of macro)
That didn't work either.
I've also experienced
compile error and expected =
There has to be a way to get this thing to work.
Thanks again for your time and any input.
Have a great afternoon.
-Helene
[quote]What do you mean the sheet doesn't have a focus?/quote]
Even though the sheet has been selected by the code, the button that started the macro still has the focus (ie: is still potentially taking input from the user).
It's a strange quirk of Excel, but it has problems changing selection within a worksheet if the focus is elsewhere (like your button). Using Activate basically sets the focus, but activating the sheet without activating the workbook doesnt always work.
I've checked my syntax for activating the book, and apparently it should be Parent rather than Workbook, ie:
Sheets("2-04 Up").Parent.Activate
or even:
ThisWorkBook.Activate
And if that doesnt work, another option to remove the problem: the code seems to be in chunks of copying from one sheet to another - there is a much simpler way (which doesnt involve selecting). Rather than this code which Excel produces in macros:
You can do this:VB Code:
VB Code:
Good monring Si -
Thanks again for the assistance. What you suggested makes sense. (Very logical. - My mind kind of thinks like a popcorn popper - and I'm left to fill in the scattered pieces.) Will let you know how it goes. Have a great day and enjoy the weekend.
-Helene:wave:
Morning again Si -
Wanted to let you know I really appreciate all the help you're giving me.
I tried what you suggested. Had a couple of kinks, worked some of them out (and you probably have an easier, less complicated way than this) and this is what I'm left with.
ThisWorkbook.Activate
Sheets("2-04 Up").Select
Range("a10:D1000").Copy
Sheets("Run ").Select
ActiveSheet.Range("A2").Select
ActiveSheet.Paste
This gets the button to work and to paste in Sheet "Run" at cell A2. However it is pasting data from the coversheet where the button is located, not the data I need from the other sheets. If I change the last line, I get run time errors again.
Thanks again.
-Helene:wave:
No worries, I appreciate being appreciated!
There still seems to be a focus problem, maybe this will work:
VB Code:
Although I would recommend this instead, as it is a bit more explicit about what you are copying (the code above allows other data to be put on the clipboard before the paste occurs):
VB Code:
Hi again Si -
I'm getting closer (and only thanks to your help!).
This seems to be working.
Sheets("2-04 Up").Range("A10:d1000").Copy
Sheets("Run ").Select
ActiveSheet.Paste
The problem is:
If I try and add other pages to be copied, I only get the last request. I need to copy from 2-04 Up thru 1-05 Up.
Has anyone ever told you that you are amazing? Helping that you don't even know. Really I could not have done this without your help. (I've been screaming at my keyboard.)
Thanks.
-Helene:wave:
Hey Si -
I think I understand.
I have a ?
In order for me to get the 2-04 sheet to paste on the run sheet, I needed to hide row a2 - a9 and paste in a10 b/c thats where the data started.
Now that I'm up to sheet 3-04, do I need to hide rows a2 - a1000 and start the data in a1001 so it will paste?
(I know it sounds like a DUH! ??, but I'm not sure if I'm missing something.)
-Helene:confused:
I'M YESING ALL OVER MY CUBICLE - THAT WORKS!!!!!!!
Application.CutCopyMode = False
Sheets("3-04 Up").Range("A1001:D2000").Copy
Sheets("Run ").Range("a1001").Select
ActiveSheet.Paste
THANK YOU SO MUCH
:bigyello: :bigyello: :bigyello: Thankyou!!Quote:
Has anyone ever told you that you are amazing? Helping that you don't even know. Really I could not have done this without your help. (I've been screaming at my keyboard.)
It kinda helps remove the "boredom" factor from my job (it gets a bit repetetive doing very similar things all the time), and I enjoy helping people.. I should've been Help Desk rather than a programmer!
cool.. does that mean it's all solved?Quote:
I'M YESING ALL OVER MY CUBICLE - THAT WORKS!!!!!!!
Hey Si -
So far so good. (However, now I have to add the pivot tables in automatically.)
I can't thank you enough.
Enjoy your weekend.
-Helene:wave:
Hi -
Reading throught this posting I realized I have a very similar issue: I was hoping someone can help me resolve it. I'm getting the error "Select Method of Range Class Failed". My code is assigned to run on the click of a button and I know the code is fine - just can't figure out why this range is not being allowed.
(Also, I took EVERYTHING out except the "bolded" range and it still gave me the error). Anyone???
Option Explicit
Dim currentRow As Range
Dim j As Integer
Private Sub cmdSubmit_Click()
ThisWorkbook.Activate
Sheets("Asian").Activate
Sheets("Asian").Select
Range("A11").Select
If cboCategory.Text = "BLEACH" Then
If ActiveCell.Value = "BLEACH" Then
For j = 0 To 500
'Put current row into current row variable
currentRow = ActiveCell.Row
'Select the current row
Rows(currentRow).Select
'Copy the current row
Selection.Copy
'Go to first empty row in sheets results
Call DoStuffToLast
'Paste currentRow to another worksheet destination
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Activate
Next
Else: ActiveCell.Offset(1, 0).Activate
End If
End If
Sheets("Results").Select
End Sub
Welcome to VBForums! :wave:
I've never like using Select and so on - they seem to be disallowed at a whim sometimes! (not to mention the fact that you are testing your luck - the user could select something else while your code is running)
Have you tried being more specific? eg:
..or even this:VB Code:
VB Code:
Hi! Thanks for your response - what I believe was the problem is that I was trying to run the sub straight from the worksheet - when I pasted the code into a module and called the sub from the worksheet then it worked fine. (Public sub). So there must be some scope issues at work here. But there's got to be a way to run .select or .activate etc...from within the current page. Hmmmm....let me know if you think of anything else, otherwise - it's working for me now. Thanks again,
Steve
Quote:
Originally Posted by si_the_geek
Hi,
I'm getting the same run time error - select method of range class failed.
Here's some of the code...
Sub Jen_FV()
Application.DisplayAlerts = False
Workbooks.Open Filename:="Path/FileName.xls"
If WorksheetExists("2- Table Chat Volume By Operat") = True Then
Worksheets("2- Table Chat Volume By Operat").Activate
Worksheets("2- Table Chat Volume By Operat").Select
Range("B13:K37").Select
Selection.Copy
Application.CutCopyMode = False
Windows("Jen.xls").Activate
Worksheets("FVRaw").Range("B40").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
If I try to run the code, the error appears in this line: Worksheets("FVRaw").Range("B40").Select
This error only appears if I run the program from a different module. Please advise.
I am new at this macro business. I have a problem similar to Helene, but I tried all the alternatives and nothing works.
I am getting a run time error (1004) in VB for Excel. I have a macro that runs fine through Alt F8. I created a control button and attached the macro. When I use the control button I get run time error 1004 with the message that says "Select method of range class failed."
this is what my macro looks like:
ThisWorkbook.Activate
Sheets("Debt Service").Activate
Sheets("Debt Service").Select
ActiveWindow.SmallScroll Down:=-9
Columns("B:IC").Select
Selection.EntireColumn.Hidden = False
Rows("1:407").Select
Rows("1:407").Select
Selection.EntireRow.Hidden = False
Columns("E:T").Select
Selection.EntireColumn.Hidden = True
Columns("Y:AW").Select
Selection.EntireColumn.Hidden = True
Columns("BC:CA").Select
Selection.EntireColumn.Hidden = True
Columns("CG:DE").Select
Selection.EntireColumn.Hidden = True
Columns("DK:EI").Select
Selection.EntireColumn.Hidden = True
Columns("EO:FM").Select
Selection.EntireColumn.Hidden = True
Columns("FS:GQ").Select
Selection.EntireColumn.Hidden = True
Columns("GW:HU").Select
Selection.EntireColumn.Hidden = True
Range("A3").Select
ActiveWindow.SmallScroll Down:=-6
Rows("8:135").Select
Selection.EntireRow.Hidden = True
Rows("138:226").Select
Selection.EntireRow.Hidden = True
Rows("229:313").Select
Selection.EntireRow.Hidden = True
Rows("317:403").Select
Selection.EntireRow.Hidden = True
Range("B2").Select
End Sub
What line of code is highlighted when you get the error?
This is the line that is highlighted Selection.EntireColumn.Hidden = False
I don't get an error there, but a better way would be to set a worksheet object and then use ranges to hide/unhide, something like this:
Code:Dim ws As Worksheet
Set ws = ActiveSheet 'or your specific sheet name, better yet
ws.Range("b1:ic1").EntireColumn.Hidden = False