Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two spreadsheets, I want the second spreadsheet to be updated once a person changes the first speradsheet. I can do this with a simple if statement but it wont update until the person working on the first sheet closes and reopens it.

Is it possible to do this automatically?

share|improve this question

I see from the comment on a deleted answer that the spreadsheets to be updated are on other computers. This update is not possible in my opinion unless one writes a macro for the remote spreadsheets that checks regularly the modification-date on the source-spreadsheet and updates itself if required. The document in this case must be on a public network share and the job is far from easy.

Your other option, if all users have access to the Internet, is to use an online spreadsheet to collaborate on just one spreadsheet. There are various online products, even free ones, that can serve this purpose.

You should supply us with more details as regarding your environment and what is wanted or possible in it.

share|improve this answer
Indeed, the problem here is that the remote sheet can be open thus not allowing it to be updated on the fly. A workaround is as you say to update it every now and then but that's on it's own nasty (the observer pattern is much more preferred these days than polling); so, I agree that it's better to use some shared / online spreadsheet instead. – Tom Wijsman Oct 5 '12 at 0:11

You can set a worksheet_change macro on the first spreadsheet that will update the second spreadsheet. The code would go something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim wbk2 As Workbook, wbk1 As Workbook, sht1 As Worksheet, sht2 As Worksheet

Set wbk1 = ActiveWorkbook
Set sht1 = ActiveSheet

on error resume next 
set wkb2=workbooks("myfile.xlsx")
if err<>0 then
    Set wbk2 = Workbooks.Open("\\remotecomputer\folder\myfile.xlsx")
end if
on error goto 0

Set sht2 = wbk2.Sheets("mySheet2")

sht2.Cells(1, 1) = "I have updated range " & Target.Address & " in sheet " & sht1.Name & " to value " & Target.Value

wbk2.Close True

End Sub
share|improve this answer
This will locally open the remote workbook and won't work if the remote sheet is already open. – harrymc Oct 3 '12 at 6:08
Updated macro if file is already open. – nutsch Oct 3 '12 at 19:21
@nutsch: I think he meant that the file might be open on the remote computer, which results in this script not working as you can't see the changes on the remote computer and perhaps overwrite them. – Tom Wijsman Oct 5 '12 at 0:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .