I would imagine that the way you push the data to the other workbook would be
unique. It would have to be built for your application.
Just to add...
This type of thing is ripe for failure. If you run the macro that updates the
receiving workbook and make a mistake, then you'll want to fix the error that
was propogated to the other workbook. This could be a miserable task to
accomplish--since there are so many ways to screw up.
Personally, I do my best to keep all my data in a single sheet of a workbook.
I'll be able to sort or filter to show the info I want.
If that's not enough, then I'd try to pull the data into the other
workbooks--maybe using =vlookup()'s and a unique key column to match data on.
If I _had_ to do it (and I still wouldn't want to!), I'd have a dedicated macro
-- not some event that does the work -- that I could run when I needed to send
the data.
Dim myCell as range
dim ToFolderName as string
dim ToWkbkName as string
Dim ToWksName as string
Dim DestCell as range
dim ToWkbk as workbook
dim ToWks as worksheet
dim ToWkbkWasOpen as boolean
set myCell = activesheet.range("A1") 'something
tofoldername = "C:\my documents\excel"
if right(tofoldername,1) <> "\" then
tofoldername = tofoldername & "\"
end if
towkbkname = "Book1.xls"
towksname = "Sheet1"
'check to see if it's open
set towkbk = nothing
on error resume next
set towkbk = workbooks(towkbkname)
on error goto 0
if towkbk is nothing then
'not open
towkbkwasopen = false
on error resume next
set towkbk = workbooks.open(filename:=towkbkname)
on error goto 0
if towkbk is nothing then
'still not open!
msgbox "file not open and couldn't be opened!"
exit sub
end if
else
towkbkwasopen = true
end if
'check to see if that sheet name exists
set towks = nothing
on error resume next
set towks = towkbk.worksheets(towksname)
on error goto 0
if towks is nothing then
msgbox "No sheet in the to workbook by that name!"
if towkbkwasopen = true then
'leave it open
else
'close it without saving
towkbk.close savechanges:=false
end if
exit sub
end if
with towks
set destcell = .range("A1") 'for example
'or to get to the next open cell in column A (after the last used cell)
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with
destcell.value = mycell.value
if towkbkwasopen then
'leave it open, maybe save it
on error resume next
towkbk.save
if err.number <> 0 then
err.clear
msgbox "Error while trying to save the already opened workbook" _
& vblf & err.number & vblf & err.description
end if
on error goto 0
else
'close it, but try to save it
on error resume next
towkbk.close savechanges:=true
if err.number <> 0 then
err.clear
msgbox "Error while trying to save and close the workbook" _
& vblf & err.number & vblf & err.description
end if
on error goto 0
end if
=========
All untested, uncompiled.
And I still wouldn't want to use something like this.
set towkbk = nothing
