-
Jul 1st, 2004, 08:55 AM
#1
Thread Starter
New Member
Excel VB Run Time Error 1004 - RESOLVED
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
Last edited by Helene; Jul 6th, 2004 at 07:32 AM.
-
Jul 1st, 2004, 09:02 AM
#2
Hyperactive Member
FWIW
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.
"The wise man doesn't know all the answers, but he knows where to find them."
VBForums is one place, but for the really important stuff ... here's a clue 1Tim3:15
-
Jul 1st, 2004, 09:07 AM
#3
Thread Starter
New Member
Thank you for your help Dave.
I'll le tyou know how it works out.
Have a great day.
-Helene
-
Jul 1st, 2004, 11:00 AM
#4
Thread Starter
New Member
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("A101000").Select
No one here seems to be able to help and I'm stuck. Any other suggestions? Thanks again.
Enjoy the day.
-Helene
I don't know why the smile comes up in the middle of the range sorry, it's a D.
-
Jul 1st, 2004, 11:19 AM
#5
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.
-
Jul 1st, 2004, 11:33 AM
#6
Thread Starter
New Member
-
Jul 1st, 2004, 11:39 AM
#7
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:
VB Code:
Private Sub CommandButton1_Click()
'
Sheets("2-04 Up").Select
[b]Sheets("2-04 Up").Activate[/b]
Range("A101000").Select
...
If not, try adding this at the start:
VB Code:
Sheets("2-04 Up").WorkBook.Activate
'or: ThisWorkBook.Activate
-
Jul 1st, 2004, 11:44 AM
#8
Thread Starter
New Member
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
-
Jul 1st, 2004, 11:54 AM
#9
Thread Starter
New Member
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
Last edited by Helene; Jul 1st, 2004 at 12:01 PM.
-
Jul 1st, 2004, 02:04 PM
#10
Thread Starter
New Member
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
-
Jul 2nd, 2004, 03:57 AM
#11
[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:
VB Code:
Sheets("2-04 Up").Select
Range("A10:D1000").Select
Selection.Copy
Sheets("Run ").Select
Range("A2").Select
ActiveSheet.Paste
You can do this:
VB Code:
Sheets("2-04 Up").Range("A10:D1000").Copy Sheets("Run ").Range("A2")
-
Jul 2nd, 2004, 07:13 AM
#12
Thread Starter
New Member
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
-
Jul 2nd, 2004, 08:23 AM
#13
Thread Starter
New Member
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("a101000").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
-
Jul 2nd, 2004, 08:41 AM
#14
No worries, I appreciate being appreciated!
There still seems to be a focus problem, maybe this will work:
VB Code:
ThisWorkbook.Activate
Sheets("2-04 Up").Activate
Sheets("2-04 Up").Select
Range("a10:d1000").Copy
Sheets("Run ").Select
ActiveSheet.Range("A2").Select
ActiveSheet.Paste
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:
ThisWorkbook.Activate
Sheets("2-04 Up").Range("A10:D1000").Copy Sheets("Run ").Range("A2")
Application.CutCopyMode = False
Sheets("3-04 Up").Range("A10:D1000").Copy Sheets("Run ").Range("A1001")
-
Jul 2nd, 2004, 09:09 AM
#15
Thread Starter
New Member
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
-
Jul 2nd, 2004, 09:18 AM
#16
Thread Starter
New Member
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
I'M YESING ALL OVER MY CUBICLE - THAT WORKS!!!!!!!
Application.CutCopyMode = False
Sheets("3-04 Up").Range("A10012000").Copy
Sheets("Run ").Range("a1001").Select
ActiveSheet.Paste
THANK YOU SO MUCH
Last edited by Helene; Jul 2nd, 2004 at 09:26 AM.
-
Jul 2nd, 2004, 10:00 AM
#17
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.)
Thankyou!!
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!
I'M YESING ALL OVER MY CUBICLE - THAT WORKS!!!!!!!
cool.. does that mean it's all solved?
-
Jul 2nd, 2004, 01:01 PM
#18
Thread Starter
New Member
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
-
Jun 27th, 2006, 04:05 PM
#19
New Member
Re: Excel VB Run Time Error 1004 - RESOLVED
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
-
Jun 27th, 2006, 06:32 PM
#20
Re: Excel VB Run Time Error 1004 - RESOLVED
Welcome to VBForums!
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:
VB Code:
Sheets("Asian").Range("A11").Select
..or even this:
VB Code:
ThisWorkBook.Sheets("Asian").Range("A11").Select
-
Jun 28th, 2006, 07:41 AM
#21
New Member
Re: Excel VB Run Time Error 1004 - RESOLVED
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
Originally Posted by si_the_geek
Welcome to VBForums!
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:
VB Code:
Sheets("Asian").Range("A11").Select
..or even this:
VB Code:
ThisWorkBook.Sheets("Asian").Range("A11").Select
-
May 28th, 2013, 05:42 PM
#22
Registered User
Re: Excel VB Run Time Error 1004 - RESOLVED
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.
-
Apr 24th, 2014, 04:13 PM
#23
New Member
Re: Excel VB Run Time Error 1004 - RESOLVED
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("CGE").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
-
Apr 25th, 2014, 06:56 AM
#24
Re: Excel VB Run Time Error 1004 - RESOLVED
What line of code is highlighted when you get the error?
-
Apr 28th, 2014, 08:20 AM
#25
New Member
Re: Excel VB Run Time Error 1004 - RESOLVED
This is the line that is highlighted Selection.EntireColumn.Hidden = False
-
Apr 28th, 2014, 08:59 AM
#26
Re: Excel VB Run Time Error 1004 - RESOLVED
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|