[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. :D
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.
:eek2:
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:
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
[b]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[/b]
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. :confused:
HELP ME!!!!!!!!!! :sick:
(Please.)
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.
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. :confused:
Re: Run-time error '1004': Select method of Range class failed
:confused: :D
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.
Re: Run-time error '1004': Select method of Range class failed
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?
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. :D
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?
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
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
Re: Run-time error '1004': Select method of Range class failed
I cut and paste the "Workbook.Worksheet.Range" into my code, and got:
Quote:
Run-time error '424':
Object required
Is this how you meant for me to do it? :
VB Code:
Worksheet.Workbook.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
:confused:
Re: Run-time error '1004': Select method of Range class failed
try
VB Code:
sheets("yoursheetnameinhere").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
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
Re: Run-time error '1004': Select method of Range class failed
Sweet! Thanks for all the help, as the main problem is now fixed!!! :D Have a secondary problem that I'd like some help with though. First, I'll post my updated code:
VB 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
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?
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
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 :D :D