Seriously Complicated


Board Regular
Sep 27, 2004
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 :pray:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
Upvote 0
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>


Upvote 0

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?

Upvote 0
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...

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back