+ Reply to Thread
Results 1 to 4 of 4

Select method of Range class failed

Hybrid View

Capo Select method of Range class... 08-09-2006, 10:27 AM
Guest RE: Select method of Range... 08-09-2006, 10:49 AM
Capo New problem. 08-09-2006, 11:02 AM
Capo problem solved! 08-09-2006, 11:27 AM
  1. #1
    Registered User
    Join Date
    08-09-2006
    Posts
    3

    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 justthe data/formatting copied over (not the formulas, as they'd still be "attached" to the original file), and JUST that one sheet. He wants to have the new sheets save just the values (along with the formatting) for archival/reference purposes.

    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?

    Private Sub cmdSave_Click()
    
            Dim SavePath As String
            Dim SaveFile As String
         
            Range("A1:G47").Select
            Selection.Copy
            Sheets("Customer").Activate
            Workbooks.Add
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
            Columns("A:G").EntireColumn.AutoFit
            Sheets(Array("Sheet2", "Sheet3")).Select
            Sheets("Sheet3").Activate
            Application.CutCopyMode = False
            ActiveWindow.SelectedSheets.Delete
            Sheets("Sheet1").Select
            Sheets("Sheet1").Name = "Procedures"
            SavePath = "N:\Procedures\Customer Info\"
            SaveFile = Range("G1")
            ActiveWorkbook.SaveAs (SavePath & SaveFile & ".xls")
            ActiveWindow.Close
            **********Range( _
                "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" _
                ).Select
            Range("B21").Activate
            Selection.ClearContents
        End Sub

    The problem appears with the "asterisked" 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; 08-09-2006 at 10:33 AM.

  2. #2
    DaveO
    Guest

    RE: Select method of Range class failed

    I can't see anything wrond with yoru code, however may i suggest you do it in
    more bite-sized chunks.

    Why not empty a row / column up at a time? This way there can't be any
    conflict over the actual differences in the row / column requirements.

    HTH.

    "Capo" wrote:

    >
    > 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 (not the formulas, as they'd
    > still be "attached" to the original file), and JUST that one sheet. He
    > wants to have the new sheets save just the values (along with the
    > formatting) for archival/reference purposes.
    >
    > 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?
    >
    >
    > Code:
    > --------------------
    > Private Sub cmdSave_Click()
    >
    > Dim SavePath As String
    > Dim SaveFile As String
    >
    > Range("A1:G47").Select
    > Selection.Copy
    > Sheets("Customer").Activate
    > Workbooks.Add
    > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    > :=False, Transpose:=False
    > Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    > SkipBlanks:=False, Transpose:=False
    > Columns("A:G").EntireColumn.AutoFit
    > Sheets(Array("Sheet2", "Sheet3")).Select
    > Sheets("Sheet3").Activate
    > Application.CutCopyMode = False
    > ActiveWindow.SelectedSheets.Delete
    > Sheets("Sheet1").Select
    > Sheets("Sheet1").Name = "Procedures"
    > SavePath = "N:\Procedures\Customer Info\"
    > SaveFile = Range("G1")
    > ActiveWorkbook.SaveAs (SavePath & SaveFile & ".xls")
    > ActiveWindow.Close
    > *Range( _
    > "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" _
    > ).Select*
    > Range("B21").Activate
    > Selection.ClearContents
    > 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.)
    >
    >
    > --
    > Capo
    > ------------------------------------------------------------------------
    > Capo's Profile: http://www.excelforum.com/member.php...o&userid=37283
    > View this thread: http://www.excelforum.com/showthread...hreadid=569904
    >
    >


  3. #3
    Registered User
    Join Date
    08-09-2006
    Posts
    3

    New problem.

    Okay, so after a few tweaks, I got the main chunks working!! :D Here is the updated code:

    Private Sub cmdSave_Click()
        Dim SavePath As String
        Dim SaveFile As String
        Dim BookName As String
        
        BookName = "test.xls"
        Range("A1:G47").Select
        Selection.Copy
        Workbooks.Add
        Selection.PasteSpecial Paste:=xlPasteValues
        Selection.PasteSpecial Paste:=xlPasteFormats
        
        Sheets(Array("Sheet2", "Sheet3")).Select
        ActiveWindow.SelectedSheets.Delete
        Sheets("Sheet1").Name = "Procedures"
       
        Columns("A:G").EntireColumn.AutoFit
    
        SavePath = "N:\Procedures\Customer Info\"
        SaveFile = Range("G1")
        ActiveWorkbook.SaveAs (SavePath & SaveFile & ".xls")
        ActiveWindow.Close
        Sheets("Customer").Select
        Sheets("Customer").Range( _
          "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" _
          ).Select
        Selection.ClearContents
        Sheets("Procedure").Select
        Range("A1").Select
        
    End Sub
    However, in the saved file, the columns aren't autofit like I had tried to do with the following line:

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

    Any suggestions there??

  4. #4
    Registered User
    Join Date
    08-09-2006
    Posts
    3

    problem solved!

    Problem is solved, posting final code, then editing title of main post. Thanks for the help guys.

    Private Sub cmdSave_Click()
        Dim SavePath As String
        Dim SaveFile As String
        If Range("G1") = "" Then
            MsgBox ("You must have a Product/Routing No. to save!")
        Else
            Range("A1:G47").Select
            Selection.Copy
            Workbooks.Add
            Selection.PasteSpecial Paste:=xlPasteValues
            Selection.PasteSpecial Paste:=xlPasteFormats
            
            Sheets(Array("Sheet2", "Sheet3")).Select
            ActiveWindow.SelectedSheets.Delete
            Sheets("Sheet1").Name = "Procedures"
           
            ActiveSheet.Columns("A:G").EntireColumn.AutoFit
        
            SavePath = "N:\Procedures\Customer Info\"
            SaveFile = Range("G1")
            ActiveWorkbook.SaveAs (SavePath & SaveFile & ".xls")
            ActiveWindow.Close
            Sheets("Customer").Select
            Sheets("Customer").Range( _
    "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" _
              ).Select
            Selection.ClearContents
            Sheets("Procedure").Select
            Range("A1").Select
        End If
        
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1