Results 1 to 26 of 26

Thread: Excel VB Run Time Error 1004 - RESOLVED

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2004
    Location
    New York
    Posts
    15

    Question 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.

  2. #2
    Hyperactive Member
    Join Date
    Apr 2001
    Location
    N42 29.340 W71 53.215
    Posts
    422

    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

  3. #3

    Thread Starter
    New Member
    Join Date
    Jul 2004
    Location
    New York
    Posts
    15
    Thank you for your help Dave.
    I'll le tyou know how it works out.
    Have a great day.
    -Helene

  4. #4

    Thread Starter
    New Member
    Join Date
    Jul 2004
    Location
    New York
    Posts
    15
    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.

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929
    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.

  6. #6

    Thread Starter
    New Member
    Join Date
    Jul 2004
    Location
    New York
    Posts
    15
    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("A101000").Select
    Selection.Copy
    Sheets("Run ").Select
    Range("A2").Select
    ActiveSheet.Paste
    Sheets("3-04 Up").Select
    Range("A101000").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

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929
    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:
    1. Private Sub CommandButton1_Click()
    2. '
    3. Sheets("2-04 Up").Select
    4. [b]Sheets("2-04 Up").Activate[/b]
    5. Range("A101000").Select
    6. ...
    If not, try adding this at the start:
    VB Code:
    1. Sheets("2-04 Up").WorkBook.Activate
    2. 'or:  ThisWorkBook.Activate

  8. #8

    Thread Starter
    New Member
    Join Date
    Jul 2004
    Location
    New York
    Posts
    15
    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

  9. #9

    Thread Starter
    New Member
    Join Date
    Jul 2004
    Location
    New York
    Posts
    15
    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.

  10. #10

    Thread Starter
    New Member
    Join Date
    Jul 2004
    Location
    New York
    Posts
    15
    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

  11. #11
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929
    [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:
    1. Sheets("2-04 Up").Select
    2. Range("A10:D1000").Select
    3. Selection.Copy
    4. Sheets("Run ").Select
    5. Range("A2").Select
    6. ActiveSheet.Paste
    You can do this:
    VB Code:
    1. Sheets("2-04 Up").Range("A10:D1000").Copy Sheets("Run ").Range("A2")

  12. #12

    Thread Starter
    New Member
    Join Date
    Jul 2004
    Location
    New York
    Posts
    15
    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

  13. #13

    Thread Starter
    New Member
    Join Date
    Jul 2004
    Location
    New York
    Posts
    15
    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

  14. #14
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929
    No worries, I appreciate being appreciated!

    There still seems to be a focus problem, maybe this will work:
    VB Code:
    1. ThisWorkbook.Activate
    2. Sheets("2-04 Up").Activate
    3. Sheets("2-04 Up").Select
    4. Range("a10:d1000").Copy
    5. Sheets("Run ").Select
    6. ActiveSheet.Range("A2").Select
    7. 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:
    1. ThisWorkbook.Activate
    2. Sheets("2-04 Up").Range("A10:D1000").Copy Sheets("Run ").Range("A2")
    3.  
    4. Application.CutCopyMode = False
    5. Sheets("3-04 Up").Range("A10:D1000").Copy Sheets("Run ").Range("A1001")

  15. #15

    Thread Starter
    New Member
    Join Date
    Jul 2004
    Location
    New York
    Posts
    15
    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

  16. #16

    Thread Starter
    New Member
    Join Date
    Jul 2004
    Location
    New York
    Posts
    15
    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.

  17. #17
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929
    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?

  18. #18

    Thread Starter
    New Member
    Join Date
    Jul 2004
    Location
    New York
    Posts
    15
    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

  19. #19
    New Member
    Join Date
    Jun 2006
    Posts
    2

    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

  20. #20
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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:
    1. Sheets("Asian").Range("A11").Select
    ..or even this:
    VB Code:
    1. ThisWorkBook.Sheets("Asian").Range("A11").Select

  21. #21
    New Member
    Join Date
    Jun 2006
    Posts
    2

    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

    Quote 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:
    1. Sheets("Asian").Range("A11").Select
    ..or even this:
    VB Code:
    1. ThisWorkBook.Sheets("Asian").Range("A11").Select

  22. #22
    Registered User
    Join Date
    May 2013
    Posts
    1

    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.

  23. #23
    New Member
    Join Date
    Apr 2014
    Posts
    2

    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

  24. #24
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Excel VB Run Time Error 1004 - RESOLVED

    What line of code is highlighted when you get the error?

  25. #25
    New Member
    Join Date
    Apr 2014
    Posts
    2

    Re: Excel VB Run Time Error 1004 - RESOLVED

    This is the line that is highlighted Selection.EntireColumn.Hidden = False

  26. #26
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    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
  •  



Click Here to Expand Forum to Full Width