ExcelBanter - Select method of Range class failed

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Select method of Range class failed (https://www.excelbanter.com/excel-programming/369864-select-method-range-class-failed.html)

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 (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

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

Select method of Range class failed

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

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??

--
Capo
------------------------------------------------------------------------
Capo's Profile: http://www.excelforum.com/member.php...o&userid=37283
View this thread: http://www.excelforum.com/showthread...hreadid=569904

Select method of Range class failed

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

Code
-------------------
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

-------------------

--
Cap
-----------------------------------------------------------------------
Capo's Profile: http://www.excelforum.com/member.php...fo&userid=3728
View this thread: http://www.excelforum.com/showthread.php?threadid=56990