Seriously Complicated

[one_more_cave_dweller]

I have a workbook where a cost is entered into a cell, the form is printed and the cost in the cell is deleted. I need a cell in my summary (seperate) workbook to link to the first workbook and return the value of the cost cell. The workbooks will not be opened at the same time in fact when the summary workbook is opened the cost will already have been deleted, and there will not be a cost entered everyday. When there is no cost entered I need the summary to keep the last cost that was entered.

Thanks ray:

 

[PA HS Teacher]

I haven't fully thought through your problem, but what if you recorded the cost in list on a separate sheet each time it changes.

 

[one_more_cave_dweller]

Unfortunately it is entered by different users and must be entered on the form.

Thanks for trying!

 

[one_more_cave_dweller]

Unfortunately it is entered by different users and must be entered on the form.

Thanks for trying!

 

[Smitty]

You'll need to adjust this for your specific range and ws, wb names, but possibly:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">With</SPAN> Application
            .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
            .DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
        
             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            <SPAN style="color:#00007F">Set</SPAN> rng = Range("A1")
            <SPAN style="color:#007F00">'   Only look at that range</SPAN>
            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            
            <SPAN style="color:#007F00">'   Transfer Cost to Summary Sheet</SPAN>
            Workbooks.Open Filename:= _
                "C:\Documents and Settings\All Users\Desktop\Temp\test1.xls"
                Sheets("Sheet1").Range("A1") = Target.Value
            ActiveWorkbook.Close <SPAN style="color:#00007F">True</SPAN>
            
            <SPAN style="color:#007F00">'   Print Form</SPAN>
            ActiveSheet.PrintOut Copies:=1
            <SPAN style="color:#007F00">'   Zero out cost</SPAN>
            Range("A1") = ""
            
            .ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
            .DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

HTH,

Smitty

 

[one_more_cave_dweller]

 

[Smitty]

So, what's the question?

If it's re: the code, right click the sheet tab and select View Code, then paste the code in the window that opens. You'll need to change things to fit like I mentioned, but start with two blank workbooks (test.xls & test1.xls works :wink: )

That code will fire whenever cell A1 in the source sheet is changed, open wb #2 and copy source A1 to summary A1, then close, print the source ws & zero the cost cell (A1).

Barring that, what's the question?

Smitty

 

[one_more_cave_dweller]

You saved me again Smitty

Thanks!!!!!! ray:

 

[Smitty]

Glad it worked!

Note that I had some real issues with the print part, but my IS Dept got a hold of my laptop this week and I've had all kinds of FUBAR issues since then.

470 print jobs sent on that one alone...Go figger...

Smitty