Programming for a save button
 

Good morning all.

I am just not very with it this morning and am having trouble with
save button on an Excel sheet. The button lives on a sheet name
"Report". The code I've been testing (that works like a charm so far
lives in ThisWorkbook (under General). The trouble seems to come whe
I copy the code from ThisWorkbook to the Report , which is where th
btnSave_Click appears when I right click on it to program it. It give
me a run-time error '1004': Select method of Range class failed when
try to run the newly copied code (F5).

The "Save" button was created with the Controls Toolbox, Command Butto
(if it matters) and I am running MSOffice with Windows 2000 (in cas
that makes a difference as well).

What I would like to be able to do is call the working code from wher
it is with the button on the Report page.

I'm sure this will be easy for most of you, and I greatly appreciat
any help offered. :)

Thank you in advance,
Cande

--
Message posted from http://www.ExcelForum.com

Programming for a save button
 

As ever, show the code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Candee " wrote in message
...
Good morning all.

I am just not very with it this morning and am having trouble with a
save button on an Excel sheet. The button lives on a sheet named
"Report". The code I've been testing (that works like a charm so far)
lives in ThisWorkbook (under General). The trouble seems to come when
I copy the code from ThisWorkbook to the Report , which is where the
btnSave_Click appears when I right click on it to program it. It gives
me a run-time error '1004': Select method of Range class failed when I
try to run the newly copied code (F5).

The "Save" button was created with the Controls Toolbox, Command Button
(if it matters) and I am running MSOffice with Windows 2000 (in case
that makes a difference as well).

What I would like to be able to do is call the working code from where
it is with the button on the Report page.

I'm sure this will be easy for most of you, and I greatly appreciate
any help offered. :)

Thank you in advance,
Candee


---
Message posted from http://www.ExcelForum.com/

Programming for a save button
 

Sorry, here it is. The following works perfectly as long as I leave i
in "ThisWorkbook". As soon as I try to move it to "Sheet1(Report) i
when the error happens. Is there a way to create a button and link i
to the code in "ThisWorkbook"?

Private Sub btnSave_Click()

Range("V5").Copy
Sheets("Name").Range("A1").PasteSpecial Paste:=xlValues
Sheets("Report").Range("E8").Copy
Sheets("Name").Range("B1").PasteSpecial Paste:=xlValues
Sheets("Report").Range("R8:W8").Copy
Sheets("Name").Range("C1").PasteSpecial Paste:=xlValues
Sheets("Report").Range("E10").Copy
Sheets("Name").Range("D1").PasteSpecial Paste:=xlValues
Sheets("Report").Range("M10:N10").Copy
Sheets("Name").Range("E1").PasteSpecial Paste:=xlValues
Sheets("Report").Range("Q10:S10").Copy
Sheets("Name").Range("F1").PasteSpecial Paste:=xlValues
Sheets("Report").Range("U10:X10").Copy
Sheets("Name").Range("G1").PasteSpecial Paste:=xlValues
Sheets("Report").Range("D12:F12").Copy
Sheets("Name").Range("H1").PasteSpecial Paste:=xlValues
Sheets("Report").Range("P12:Q12").Copy
Sheets("Name").Range("I1").PasteSpecial Paste:=xlValues
Sheets("Report").Range("C17:X17").Copy
Sheets("Name").Range("K1").PasteSpecial Paste:=xlValues
Sheets("Report").Range("I19:M19").Copy
Sheets("Name").Range("L1").PasteSpecial Paste:=xlValues
Range("D2").Copy
Range("D1").PasteSpecial Paste:=xlValues
Range("J2").Copy
Range("J1").PasteSpecial Paste:=xlValues
Sheets("Report").Select
'Open log file
ChDir "\\Nfil108\QA\Shared Files\Testing"
Workbooks.Open FileName:= _
"\\Nfil108\QA\Shared Files\Testing\Interdivisional Proble
Report.xls"
'Insert the line for the new data
ActiveWorkbook.Sheets("Issue Log").Activate
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("A2").Select
'Copy data from the report file to the log file
Windows("IPR Test").Activate
ActiveWorkbook.Sheets("Name").Activate
Range("A1:U1").Copy
Workbooks("Interdivisional Problem Report.xls").Activate
Range("A2").PasteSpecial Paste:=xlValues
Range("A2").Select
Application.CutCopyMode = False
Range("A2:U300").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A2").Select
ActiveWorkbook.Save
ActiveWindow.Close
'Call the procedure to save a copy of the file
FName
'Clear the entries made to the report and save it for next use
Range("B1:U1").ClearContents
Sheets("Report").Range("E8").ClearContents
Range("E10").ClearContents
Range("M10:N10").ClearContents
Range("Q10:S10").ClearContents
Range("U10:X10").ClearContents
Range("D12:F12").ClearContents
Range("P12:Q12").ClearContents
Range("I19:M19").ClearContents
Range("C17:X17").ClearContents
ActiveWorkbook.Sheets("Report").Select
ActiveWorkbook.Save

End Sub


Public Sub FName()

Dim FName As String

FName = "\\Nfil108\qa\Shared Files\Testing\" & "IPR "
Range("A1").Value & ".xls"
ThisWorkbook.SaveCopyAs FileName:=FName

Application.Dialogs(xlDialogSendMail).Sho
")
End Su

--
Message posted from http://www.ExcelForum.com

Programming for a save button
 

"Candee " wrote in message
...
Sorry, here it is. The following works perfectly as long as I leave it
in "ThisWorkbook". As soon as I try to move it to "Sheet1(Report) is
when the error happens. Is there a way to create a button and link it
to the code in "ThisWorkbook"?


You could by changing it to a public sub and add this to the button click
event on the sheet

ThisWorkbook.btn_save_Click

but I wouldn't recommende it. If there is a problem, just calling it from a
different place won't solve it, you need to get to the root of the problem.

I would start by adding a Sheets qualifier to all of of the unqualified
Range statements, and then running again. Preferably, step through it to see
where it goes wrong.

Programming for a save button
 

Thank you Bob.

For a quick fix for now, I did put in ThisWorkbook.btnSave_Click and i
runs as it should. When I have more time to dig, I will explore t
find out why the other way doesn't work.

Thanks again, and have a great day

--
Message posted from http://www.ExcelForum.com

Programming for a save button
 

I love it when people ignore my advice and yet they et what they want<VBG

Bob

"Candee " wrote in message
...
Thank you Bob.

For a quick fix for now, I did put in ThisWorkbook.btnSave_Click and it
runs as it should. When I have more time to dig, I will explore to
find out why the other way doesn't work.

Thanks again, and have a great day!


---
Message posted from http://www.ExcelForum.com/