Results 1 to 15 of 15

Thread: [SOLVED] Run-time error '1004': Select method of Range class failed

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2006
    Posts
    8

    [SOLVED] Run-time error '1004': Select method of Range class failed

    Hi everyone, first time poster (as many people who are at their wit's end are, I'm sure) here. I did a search for the problem I'm having, but from the posts I found, it's kind of a vague problem, so I didn't really find a solution to what I'm having problems with. I'll give a quick rundown of what I'm trying to do, then a quick rundown of the code, then, uh, the floor is yours.

    Basically, I've got a workbook with three sheets in it. One of the sheets has a lot of stuff that is dependant on information put into another sheet. For example, customer name, different quantities ordered, measurements, etc. The second sheet does calculations on that, and makes a "customer info"/"work order" type sheet. My task is to create a "Save" button that takes the second sheet, copies it into a new workbook, and saves it as one of the field names. It needs to be just the data/formatting copied over, and JUST that one sheet.

    I'm also to have the original file (the one that has the Save button) clear the first sheet, where the data gets entered, not calculated.



    So. Here's my entire code for the Save button. It's probably horribly sloppy and completely inefficient, but hey, that's why I'm posting here, right?

    VB Code:
    1. Private Sub cmdSave_Click()
    2.  
    3.     Dim SavePath As String
    4.     Dim SaveFile As String
    5.  
    6.     Range("A1:G47").Select
    7.     Selection.Copy
    8.     Sheets("Customer").Activate
    9.     Workbooks.Add
    10.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    11.         :=False, Transpose:=False
    12.     Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    13.         SkipBlanks:=False, Transpose:=False
    14.     Columns("A:G").EntireColumn.AutoFit
    15.     Sheets(Array("Sheet2", "Sheet3")).Select
    16.     Sheets("Sheet3").Activate
    17.     Application.CutCopyMode = False
    18.     ActiveWindow.SelectedSheets.Delete
    19.     Sheets("Sheet1").Select
    20.     Sheets("Sheet1").Name = "Procedures"
    21.     SavePath = "N:\Procedures\Customer Info\"
    22.     SaveFile = Range("G1")
    23.     ActiveWorkbook.SaveAs (SavePath & SaveFile & ".xls")
    24.     ActiveWindow.Close
    25.     [b]Range( _
    26.         "B1,G1,B2,B3,G3,B5,B6,B7,B8,I5,I6,I7,I8,I9,G13,G15,G17,G19,B13,B14,B15,B16,B19,B20,B21" _
    27.         ).Select[/b]
    28.     Range("B21").Activate
    29.     Selection.ClearContents
    30. End Sub

    The problem appears with the bolded line. I'm not sure why, as when I created a macro, and ran through this step by step, that's what I got.

    HELP ME!!!!!!!!!!

    (Please.)
    Last edited by Capo; Aug 9th, 2006 at 10:22 AM. Reason: Problem solved! :D

  2. #2
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Run-time error '1004': Select method of Range class failed

    To bypass most of that code you can do an .Add for adding the new workbook and then do a .Copy on the one sheet you want telling it the destination of the newly added workbook. Then saveas and your dont.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2006
    Posts
    8

    Re: Run-time error '1004': Select method of Range class failed

    Quote Originally Posted by RobDog888
    To bypass most of that code you can do an .Add for adding the new workbook and then do a .Copy on the one sheet you want telling it the destination of the newly added workbook. Then saveas and your dont.
    The only thing is, the data will still be "attached" to the original file. I just want the values/formatting copied over.

    Just as a simple example, one of the cells in the sheet I want to copy over is something like: IF(Customer!I5<>"",Customer!I5,"")

    But if I just straight copy the whole sheet into the new book, it would become:

    IF([name.xls]Customer!I5<>"",[name.xls]Customer!I5,"").

    Whereas I just want it to copy whatever the value is at the time of clicking the custom Save button.

    If that made ANY sense.

  4. #4
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Run-time error '1004': Select method of Range class failed



    Well try this. Right click on the sheets tab and click copy, paste it into a new workbook and see if it gives you what you want.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  5. #5

    Thread Starter
    New Member
    Join Date
    Aug 2006
    Posts
    8

    Re: Run-time error '1004': Select method of Range class failed

    No luck.

  6. #6
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Run-time error '1004': Select method of Range class failed

    Not exactly sure why or what you need out of the sheet as you state you need the value but point out its formula?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  7. #7

    Thread Starter
    New Member
    Join Date
    Aug 2006
    Posts
    8

    Re: Run-time error '1004': Select method of Range class failed

    Well the first sheet is where my boss inputs his data. Customer name, order type, width, thickness, etc.

    The second sheet takes that data, and in a lot of cases, just copies the values over (Name, for instance.).

    But, depending on certain values he inputs, the calculated values will differ. If one of the values is higher than a certain value, the calculations done are different, etc.

    He wants the Save button to just copy the calculated/etc information from the second sheet into a new workbook, primarily for archival purposes. Thus, having it as a formula doesn't make sense.

    Hmm. As I write this.. Maybe instead of isolating the information/sheet in a new workbook and THEN saving.. Maybe if I remove the other two sheets, convert the formulas to values, then SaveAs, then reopen the original workbook? Sounds kinda convoluted this way too.. But meh, I don't care, if it works, that's what matters.

  8. #8
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Run-time error '1004': Select method of Range class failed

    Well what I dont understand is that if you copy the sheet and paste it to a new workbook it gives me the value by having the formula reference the original book. So could you still do a copy/paste and then conver t the new books values to values and not formulas?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  9. #9

    Thread Starter
    New Member
    Join Date
    Aug 2006
    Posts
    8

    Re: Run-time error '1004': Select method of Range class failed

    That could work too. But the thing is, the problem isn't even WITH any of that stuff. It's the selection of the cells in the original workbork's "entry" sheet in preparation to delete them. lol

  10. #10
    Frenzied Member zaza's Avatar
    Join Date
    Apr 2001
    Location
    Borneo Rainforest Habits: Scratching
    Posts
    1,486

    Re: Run-time error '1004': Select method of Range class failed

    The reason that the Range part doesn't work is that you haven't specified the workbook and sheet in which you want to select the range. This sort of coding will work as long as you aren't chopping and changing around with worksheets, but in this case you are. To be safe rather than sorry, always use the full address: Workbook.Worksheet.Range.


    If you cut and paste this line into a button in a new sheet, it'll work. Hence the problem is not the syntax - it just doesn't know where to find the Range.


    Incidentally, if you look at what your code is doing, you'll see that you select it and then clear the selection. You could just as easily set the Range to "".

    zaza
    I use VB 6, VB.Net 2003 and Office 2010



    Code:
    Excel Graphing | Excel Timer | Excel Tips and Tricks | Add controls in Office | Data tables in Excel | Gaussian random number distribution (VB6/VBA,VB.Net) | Coordinates, Vectors and 3D volumes

  11. #11

    Thread Starter
    New Member
    Join Date
    Aug 2006
    Posts
    8

    Re: Run-time error '1004': Select method of Range class failed

    I cut and paste the "Workbook.Worksheet.Range" into my code, and got:

    Run-time error '424':

    Object required
    Is this how you meant for me to do it? :

    VB Code:
    1. Worksheet.Workbook.Range( _
    2.       "B1,B2,B3,B5,B6,B7,B8,B13,B14,B15,B16,B19,B20,B21,G1,G3,I5,I6,I7,I8,I9,G13,G15,G17,G19" _
    3.       ).Select


  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Run-time error '1004': Select method of Range class failed

    try
    VB Code:
    1. sheets("yoursheetnameinhere").Range( _
    2.       "B1,B2,B3,B5,B6,B7,B8,B13,B14,B15,B16,B19,B20,B21,G1,G3,I5,I6,I7,I8,I9,G13,G15,G17,G19" _
    3.       ).Select
    also omit Range("B21").Activateas that change the selection to a single cell (i presume you want to clear all cells in the range above)

    probably you also don't need or want these 2 lines
    Sheets("Sheet3").Activate
    Application.CutCopyMode = False

    also do you know what window (if any) you have open after
    ActiveWorkbook.SaveAs (SavePath & SaveFile & ".xls")
    ActiveWindow.Close
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13

    Thread Starter
    New Member
    Join Date
    Aug 2006
    Posts
    8

    Re: Run-time error '1004': Select method of Range class failed

    Sweet! Thanks for all the help, as the main problem is now fixed!!! Have a secondary problem that I'd like some help with though. First, I'll post my updated code:

    VB Code:
    1. Private Sub cmdSave_Click()
    2.     Dim SavePath As String
    3.     Dim SaveFile As String
    4.     Dim BookName As String
    5.    
    6.     BookName = "test.xls"
    7.     Range("A1:G47").Select
    8.     Selection.Copy
    9.     Workbooks.Add
    10.     Selection.PasteSpecial Paste:=xlPasteValues
    11.     Selection.PasteSpecial Paste:=xlPasteFormats
    12.    
    13.     Sheets(Array("Sheet2", "Sheet3")).Select
    14.     ActiveWindow.SelectedSheets.Delete
    15.     Sheets("Sheet1").Name = "Procedures"
    16.    
    17.     Columns("A:G").EntireColumn.AutoFit
    18.  
    19.     SavePath = "N:\Procedures\Customer Info\"
    20.     SaveFile = Range("G1")
    21.     ActiveWorkbook.SaveAs (SavePath & SaveFile & ".xls")
    22.     ActiveWindow.Close
    23.     Sheets("Customer").Select
    24.     Sheets("Customer").Range( _
    25.       "B1,B2,B3,B5,B6,B7,B8,B13,B14,B15,B16,B19,B20,B21,G1,G3,I5,I6,I7,I8,I9,G13,G15,G17,G19" _
    26.       ).Select
    27.     Selection.ClearContents
    28.     Sheets("Procedure").Select
    29.     Range("A1").Select
    30.    
    31. End Sub

    Now, everything cuts/saves/clears okay, but the problem is that the file that gets saved.. The columns aren't autofitted like I tried to do with this:

    Columns("A:G").EntireColumn.AutoFit

    Any ideas on how I can get that to work?

  14. #14
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Run-time error '1004': Select method of Range class failed

    try sheets("sheet1") or activesheet.Columns("A:G").EntireColumn.AutoFit
    depending which sheet is the one you want to autofit, it may be doing the autofit on the wrong sheet, as you just deleted the selected sheets, i would have no idea as to which would be the activesheet, so best you address it by name
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  15. #15

    Thread Starter
    New Member
    Join Date
    Aug 2006
    Posts
    8

    Re: Run-time error '1004': Select method of Range class failed

    You totally rock! Problems solved, case closed, I love you all, and see you next time I have a problem! <3 <3 <3

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