Updating links upon macro, my thanks to this forum
 

I have two workbooks used separately by two people in different ends of a
building. The first is purely data entry to a separate sheet in the same
workbook. The second pulls from that data with a simple link in a hidden
cell and runs a macro on that data. The problem is that the data linked does
not update each time the macro runs, and I'd like it to as the data entry
workbook may be amended and saved while the other remains open. I'd like to
not have the macro write the links, reason being that the "pulling" workbook
can be used by any number of personal PCs with network drives mapped
differently. Remapping everyone's drives is an option, but is there an
easier way to get links to another workbook to update to current values with
a macro?

I'd also like to take the opportunity to thank anyone who has helped me in
the past - since I started a new job and started learning about VBA and excel
I've effectively freed two people entirely (8hrs/day) from doing paperwork
and, last I checked, I've saved about 500 lbs of paper per year all while
making ISO audits easier and allowing myself to be lazier! I appreciate
having this forum when I get stuck with something, as above. Thanks.

Updating links upon macro, my thanks to this forum
 

In xl2003 menus, I can use:
Edit|Links|Update values

If I needed a macro, I could record one when I did it manually.

If I only need a single cell updated, I could use something like:

dim myCell as range
set mycell = somerangehere
with mycell
.formula = .formula
end with

And then excel would reevaluate the formula in that cell.

If I had a limited number of cells, I could record a macro while:

I selected that range
Edit|Replace
what: = (equal sign)
with: =
replace all



seed wrote:

I have two workbooks used separately by two people in different ends of a
building. The first is purely data entry to a separate sheet in the same
workbook. The second pulls from that data with a simple link in a hidden
cell and runs a macro on that data. The problem is that the data linked does
not update each time the macro runs, and I'd like it to as the data entry
workbook may be amended and saved while the other remains open. I'd like to
not have the macro write the links, reason being that the "pulling" workbook
can be used by any number of personal PCs with network drives mapped
differently. Remapping everyone's drives is an option, but is there an
easier way to get links to another workbook to update to current values with
a macro?

I'd also like to take the opportunity to thank anyone who has helped me in
the past - since I started a new job and started learning about VBA and excel
I've effectively freed two people entirely (8hrs/day) from doing paperwork
and, last I checked, I've saved about 500 lbs of paper per year all while
making ISO audits easier and allowing myself to be lazier! I appreciate
having this forum when I get stuck with something, as above. Thanks.

--

Dave Peterson