Update between two workbooks.........

I am working on a worksheet that relies on data from a log that is
kept on another workbook.  The log workbook is updated constantly by a
team of 12 people and no two people can be in the log at the same
time.  So it works very nicely for that purpose.

I am builidng in another ' calculation ' workbook a worksheet that
will use the data from the log workbook and the 3 fields from the log
will be used as descriptions from one entry from the log.   For ease
of update I would use a second worksheet in the calculation workbook
to hold the entire contents of the original worksheet from the log. as
the one entry could be from any row from the log.

What I would like to happen ideally is that every time the log
workbook is closed, the contents of the log page are updated to the
page I will work from in the calcualtion workbook.
0
10/14/2009 10:05:03 PM
excel 39879 articles. 2 followers. Follow

3 Replies
333 Views

Similar Articles

[PageSpeed] 50

I think you want more than this, but this macro does what you say you want. 
I chose "Calculation.xls" as the name of the workbook into which you want 
the log pasted.  I assumed that the Calculation workbook has a sheet named 
"Log Sht" and that the log data will be pasted to that sheet.  I also 
assumed that the Calculation workbook is open.  I also assumed that the log 
workbook has a sheet named "Log".  Make changes to this macro as needed to 
go with what you have.

This macro must be placed in the workbook module of the log workbook.  This 
macro fires when the log workbook is closed.  The macro does the following:

Clears all cells in the Calculation workbook, sheet "Log Sht".

Copies all the cells in the Log sheet of the log file.

Pastes into A1 of the "Log Sht" of the Calculation workbook.  Post back if 
you need more.  HTH  Otto

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim wbCalc As Workbook

    Sheets("Log").Select

    Set wbCalc = Workbooks("Calculation.xls")

    With wbCalc.Sheets("Log Sht")

        .Cells.ClearContents

        Cells.Copy .Range("A1")

    End With

End Sub

"M G Henry" <mr_mikehenry007@hotmail.com> wrote in message 
news:5f62bed8-1eb5-4848-aa60-6d175760ebac@z24g2000yqb.googlegroups.com...
>I am working on a worksheet that relies on data from a log that is
> kept on another workbook.  The log workbook is updated constantly by a
> team of 12 people and no two people can be in the log at the same
> time.  So it works very nicely for that purpose.
>
> I am builidng in another ' calculation ' workbook a worksheet that
> will use the data from the log workbook and the 3 fields from the log
> will be used as descriptions from one entry from the log.   For ease
> of update I would use a second worksheet in the calculation workbook
> to hold the entire contents of the original worksheet from the log. as
> the one entry could be from any row from the log.
>
> What I would like to happen ideally is that every time the log
> workbook is closed, the contents of the log page are updated to the
> page I will work from in the calcualtion workbook. 


0
10/15/2009 3:00:45 PM
On Oct 15, 9:00=A0am, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
wrote:
> I think you want more than this, but this macro does what you say you wan=
t.
> I chose "Calculation.xls" as the name of the workbook into which you want
> the log pasted. =A0I assumed that the Calculation workbook has a sheet na=
med
> "Log Sht" and that the log data will be pasted to that sheet. =A0I also
> assumed that the Calculation workbook is open. =A0I also assumed that the=
 log
> workbook has a sheet named "Log". =A0Make changes to this macro as needed=
 to
> go with what you have.
>
> This macro must be placed in the workbook module of the log workbook. =A0=
This
> macro fires when the log workbook is closed. =A0The macro does the follow=
ing:
>
> Clears all cells in the Calculation workbook, sheet "Log Sht".
>
> Copies all the cells in the Log sheet of the log file.
>
> Pastes into A1 of the "Log Sht" of the Calculation workbook. =A0Post back=
 if
> you need more. =A0HTH =A0Otto
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>
> =A0 =A0 Dim wbCalc As Workbook
>
> =A0 =A0 Sheets("Log").Select
>
> =A0 =A0 Set wbCalc =3D Workbooks("Calculation.xls")
>
> =A0 =A0 With wbCalc.Sheets("Log Sht")
>
> =A0 =A0 =A0 =A0 .Cells.ClearContents
>
> =A0 =A0 =A0 =A0 Cells.Copy .Range("A1")
>
> =A0 =A0 End With
>
> End Sub
>
> "M GHenry" <mr_mikehenry...@hotmail.com> wrote in messagenews:5f62bed8-1e=
b5-4848-aa60-6d175760ebac@z24g2000yqb.googlegroups.com...
>
>
>
> >I am working on a worksheet that relies on data from a log that is
> > kept on another workbook. =A0The log workbook is updated constantly by =
a
> > team of 12 people and no two people can be in the log at the same
> > time. =A0So it works very nicely for that purpose.
>
> > I am builidng in another ' calculation ' workbook a worksheet that
> > will use the data from the log workbook and the 3 fields from the log
> > will be used as descriptions from one entry from the log. =A0 For ease
> > of update I would use a second worksheet in the calculation workbook
> > to hold the entire contents of the original worksheet from the log. as
> > the one entry could be from any row from the log.
>
> > What I would like to happen ideally is that every time the log
> > workbook is closed, the contents of the log page are updated to the
> > page I will work from in the calcualtion workbook.- Hide quoted text -
>
> - Show quoted text -

Otto,

Everything is workable except the workbook Calculation is not
necessarily open at the same time as the log workbook.

I was also thinking of a possible conflict.... if someone is in the
workbook Calcuation when the log workbook is closed, would it present
a problem to the fact that the person in the workbook Calculation
would be selecting a line of data when the macro from the log workbook
deletes the log sht worksheet contents in the Calcuation workbook ???

Thanks for your help thus far.....
0
10/21/2009 9:02:11 PM
Obviously that would be a problem.  If you can't manage the state of the 
Calculation file when the log file is closed, then you cannot use the 
Before_Close event as the trigger.  The only solution I can see, in that 
circumstance, is to manually trigger the update where and when you have 
control of both files.  You would then use the following macro.  Note that 
only the first line (the macro name) is changed.
HTH  Otto
Sub UpdateCalc
    Dim wbCalc As Workbook
    Sheets("Log").Select
    Set wbCalc = Workbooks("Calculation.xls")
    With wbCalc.Sheets("Log Sht")
        .Cells.ClearContents
        Cells.Copy .Range("A1")
    End With
End Sub
"M G Henry" <mr_mikehenry007@hotmail.com> wrote in message 
news:c7f1f7d9-04e3-44de-bebe-61527696a78c@a6g2000vbp.googlegroups.com...
On Oct 15, 9:00 am, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
wrote:
> I think you want more than this, but this macro does what you say you 
> want.
> I chose "Calculation.xls" as the name of the workbook into which you want
> the log pasted. I assumed that the Calculation workbook has a sheet named
> "Log Sht" and that the log data will be pasted to that sheet. I also
> assumed that the Calculation workbook is open. I also assumed that the log
> workbook has a sheet named "Log". Make changes to this macro as needed to
> go with what you have.
>
> This macro must be placed in the workbook module of the log workbook. This
> macro fires when the log workbook is closed. The macro does the following:
>
> Clears all cells in the Calculation workbook, sheet "Log Sht".
>
> Copies all the cells in the Log sheet of the log file.
>
> Pastes into A1 of the "Log Sht" of the Calculation workbook. Post back if
> you need more. HTH Otto
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
>
> Dim wbCalc As Workbook
>
> Sheets("Log").Select
>
> Set wbCalc = Workbooks("Calculation.xls")
>
> With wbCalc.Sheets("Log Sht")
>
> .Cells.ClearContents
>
> Cells.Copy .Range("A1")
>
> End With
>
> End Sub
>
> "M GHenry" <mr_mikehenry...@hotmail.com> wrote in 
> messagenews:5f62bed8-1eb5-4848-aa60-6d175760ebac@z24g2000yqb.googlegroups.com...
>
>
>
> >I am working on a worksheet that relies on data from a log that is
> > kept on another workbook. The log workbook is updated constantly by a
> > team of 12 people and no two people can be in the log at the same
> > time. So it works very nicely for that purpose.
>
> > I am builidng in another ' calculation ' workbook a worksheet that
> > will use the data from the log workbook and the 3 fields from the log
> > will be used as descriptions from one entry from the log. For ease
> > of update I would use a second worksheet in the calculation workbook
> > to hold the entire contents of the original worksheet from the log. as
> > the one entry could be from any row from the log.
>
> > What I would like to happen ideally is that every time the log
> > workbook is closed, the contents of the log page are updated to the
> > page I will work from in the calcualtion workbook.- Hide quoted text -
>
> - Show quoted text -

Otto,

Everything is workable except the workbook Calculation is not
necessarily open at the same time as the log workbook.

I was also thinking of a possible conflict.... if someone is in the
workbook Calcuation when the log workbook is closed, would it present
a problem to the fact that the person in the workbook Calculation
would be selecting a line of data when the macro from the log workbook
deletes the log sht worksheet contents in the Calcuation workbook ???

Thanks for your help thus far..... 


0
10/21/2009 10:45:17 PM
Reply:

Similar Artilces:

Pulsar Women's Crystal Accented Dress Two-Tone Stainless Steel Watch #PEGA16
Price:$125.00 Image: http://bestdeallocator.info/image.php?id=B001L1RZ8C Best deal: http://bestdeallocator.info/index.php?id=B001L1RZ8C This ladies' two tone, Pulsar fashion watch features a white dial CrystallizedTM with 22 Swarovski crystals. Watch is water resistant up to 30 meters. This stylish timepiece features a two-tone bracelet and case. The mother-of-pearl dial is accentuated by gold-tone hands and markers. 22 Swarovski® crystals sparkle on this classic watch. 30M water resistant. ACCESSORIES: Pulsar Women's Crystal Accented Dress Two-Tone Stainless Steel W...

managing two identities
need some help folks: i set up two identities with outlook express 5 and in general the download from both accounts works fine, but whenever i try to switch from the main identity to my second identity the download would not work and then, when trying to switch again back or quit, the program stops working and i get a message soundling like 'outlook express could not be started because another instance of outlook express is still running' any leads how to get two identities work smoothly? cheers and thanks "Gernot" <h9851633@wu.edu> wrote in message news:5f7001c3b34...

Two profiles sharing one email account/pst?
I have a laptop, Win XP Pro & Office XP, that I use at work logging in as user/domain. At home I log in as user/laptop. The first time I used it at home & tried to check mail (mail was originally set up at work), it went through the motions to set up a new email account. Surely, I can share the same mail account or pst between profiles, but how? Thanks find the pst from work on your computer - if windows explorer, tools, options, view hidden files and folders is not set to show hidden files and folders, change it and then browse to C:\Documents and Settings\work_username\Local Setti...

Incorrect Office Updates in Microsoft Update
Hello I recently did clean installs of XP and Win7 on 2 different PCs. Each PC was loaded with Office 2007. When I ran Microsoft Update after the Office installs, it shows I needed Office 2007 SP1, not SP2. I manually downloaded and installed Office 2007 SP2 and then checked Windows Updates for any needed patches. It still showed I needed Office 2007 SP1...even though I just installed SP2. Does anyone know how to 1) have Office SP2 show up as a needed update instead of SP1, and 2) how to configure Windows Update to show that no SP is needed after SP2 is installed? th...

Two mailboxes for one user account after mailbox moved to a new server
After moving a user's mailbox from a Exchange 2003 to a new Exchange 2003 server, Outlook has listed two mailboxes with same name in the "All Mail Folders"area. The Outlook profile only has the user mailbox and no other mailboxes added to it. The user can send and recieve email just fine. The mailboxes seem to be clones of each other. New mail show up in the mailboxes at the sametime. How can I get Outlook to only show one mailbox? Any help would be appreciated, thanks, ...

Update Manager
Everytime I boot up my PC a small window pops up entitled "UPDATE MANAGER". It shows two progress bars, but nothing ever happens. Why is this window popping up every tiem I turn on the computer and how do I get it to stop? It never used to do this, it just statred lately. Thankyou. tony Because you installed something which features an update manager "Omaha Tony" <OmahaTony@discussions.microsoft.com> wrote in message news:A08659C0-1560-4E8C-8A1C-76A5B1DBB1F8@microsoft.com... > Everytime I boot up my PC a small window pops up entitled "UP...

Re: Update for Windows Vista (KB980248) -- No more fonts available!
cf. http://social.answers.microsoft.com/Forums/en-US/vistawu/thread/f7fa37ca-683c-4be1-b4ad-ab2dd1249399 PA Bear [MS MVP] wrote: > Are you running Vista SP1 or Vista SP2? Is Office 2010 installed? > > Was KB980248 offered & eventually installed via Windows Update or did you > (attempt to) install it manually? > > What anti-virus application or security suite is installed and is your > subscription current? What anti-spyware applications (other than > Defender)? > What third-party firewall (if any)? > > Has a(another) Norton or McAfee ap...

automatic office updates
Is there a way to configure a PC to receive Office Updates automatically like Windows Update? Thanks. Jeff Via MicrosoftUpdate as apposed to windowsupdate, and then configuring how you update. eg automatic or download and notify "J Miller" <jmiller@*nospam*pcsdps.org> wrote in message news:456db17c$0$81355$ae4e5890@news.nationwide.net... > Is there a way to configure a PC to receive Office Updates automatically > like Windows Update? > > Thanks. > > > Jeff > > Yes. Microsoft updates does this. What version of Office do you use? -- Diane...

does vista installed on virtual machine 2007 get wsus updates ?
It is searching for updates but it is not finding anything and saying that Windows is up to date. I have set the updates to install from the wsus server and assigned the updates to the same Vista virtual machine .. Thank you -- aconti ------------------------------------------------------------------------ aconti's Profile: http://forums.techarena.in/members/73272.htm View this thread: http://forums.techarena.in/active-directory/1290161.htm http://forums.techarena.in Hello aconti, If the machine is getting the correct GPO for the WSUS settings, check with rsop...

Outlook 2000 doesn't respond after downloading MS update!!
Luckily, my Outlook Express seems to work, but my Outlook 2000 will open, but if I click on a message or do Anything, it FREEZES. HELP!! I rely on calendar functions, other things. HOW could MS allow an update that screws up their own programs? I did restore - to no avail. I lent CD to someone, so can't do "repair." (Trying to get a hold of friend) Please help! I'm in process of job hunt & really need Outlook to function... Thanks, Sherry Did you recently apply SP3 for Office/Outlook 2000 and is Outlook 2000 configured in Internet Mail Only mode? If yes, you...

my workbook is too big
i have a workbook with several sheets of data on it, not much like 15 columns and 20 rows full of text, columns are a little wider than normal, but not one over twice its original size. my main data sheet is a table. i copied the format of the whole sheet, along with the 1st row of the sheet to 12 other sheets, so i have 13 identical sheets, but only one has data on it right now. when i saved it, the workbook was like almost 400k. no formulas are copied any further down than the 20th row of the main sheet. i have many macros running on the one main sheet but thats all, when i a...

Sending a workbook as a mail attachment
Hey Folks, Is there an easy way to send a workbook via email based on the date an time? Our company uses excel for some of our reporting and rather then having the reps remember to send the workbook i'd like to have it send itself automatically - say on Saturdays or Sundays. Ideas? Nel post news:1165167749.298713.321880@l12g2000cwl.googlegroups.com *Gord* ha scritto: > Hey Folks, > > Is there an easy way to send a workbook via email based on the date an > time? Our company uses excel for some of our reporting and rather > then having the reps remember to send the wor...

Change links to hyperlinks in a workbook?
I have 5 Income Statement sheets in a workbook. The first sheet is an Income Statement summing four departments (listed by columns). Column B has Dept 1 data, column C has Dept 2 data, etc. ALL data on this sheet is linked (referenced) to the other four sheets which are the individual departments. I know I can set hyperlinks one at a time, but is there a way for me to have each of those links (every cell containing the data from the other four sheets) become hyperlinks? If I have to add another sheet that mirrors the summary sheet but has hyperlinks, that would be fine. I am just ...

Comparing first and last names in two lists
I have two data sources that each contain about 8000 names in seperate fields for first and last name. I can put them on seperate worksheets or append one to the other. I need to make them match and find out where they don't. Messed with consolidate but not sure if that will work for more than one column at a time. FYI data set one is from a school transportation database and data set two is from the main student database from the same school district. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages dir...

can't update office xp
I can't download the latest updates for office xp. I get a "hotfix" error. I also can't have microsoft check my computer for installed updates. it just tells me that my computer is unable to communicate with microsoft. >-----Original Message----- >I can't download the latest updates for office xp. I get >a "hotfix" error. I also can't have microsoft check my >computer for installed updates. it just tells me that my >computer is unable to communicate with microsoft. >. >guess no one has any reply to this, should I reload outloo...

Error saving a Shared Workbook
I have a user using a shared workbook and gets an error everytime she does this ONE specific change. She deletes a line and then saves and gets a message that says, "EXCEL.exe has generated errors and will be shutdown by Windows... blah blah blah". If she makes anyother type of changes it saves just fine. I also had another user do the SAME change and that person also got that error message. When the file is changed to "Not Shared" then the line is deleted it saves fine also. The problem is obviously with the file itself and that is shared out. can anybody ...

How do I break URL address over two lines?
I am trying to enter website addresses into footnotes and Word automatically forces the footnote onto a separate line. I need to conform to certain aditing requirements and want the url to start right after the citation and continue on to however many lines are necessary, but not leave a gap because it starts the url on a separate line in the footnote. Help please. In the edit hyperlink dialog, put the URI in both the Address and Text-to- display boxes. That way you can use a space or linebreak to wrap the display text to the next line without affecting the hyperlink ability. Hyp...

Need help with update sql plus filter
I have the following update sql (copied from the query design view) UPDATE ListQry SET ListQry.ApprovalStatusID = [Forms]![OpeningForm]![Responsibility] WHERE (((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility] And (ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID)>300)) OR (((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility] And (ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID) Is Null)); ApprovalStatusID is an integer OtherStatusID is an integer ListQry is the recordsource for my form. I would like to add the f...

Open Workbook
I have a query that pulls 2 fields from a table. One field (ReportName) is selected from a form combo box. The second field (ReportLocation) in the query is the full path of the ReportName selected. I want to open the workbook using the ReportLocation, when the ReportName in the form changes. So I need to the code to enter in "On Change" for my form, so whatever report is selected, it opens up. Please help. Thank you in advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200802/1 here is my current code that doesn'...

Excel link update question
I need to maintain an excel workbook which contains a lot of links to other workbooks. Since the linked workbooks change every week, I need to change all the links accordingly. For example, a cell with formula "='[aug_28.xls]sheet1' !A10" will be changed into "='[sep_4.xls]sheet1'!A10". I tried to do this with Find/Replace. However, the link is updated every time it is changed. The link updating takes a couple of seconds. So it may take a hour to finsh it for a workbook with thousands of links. Could anyone tell me how to shut down the link updating when...

Update Query
Access 2003 XP SP2 I am having a problem with an update query. Table is in a one-to-one relationship, referentail integrity and cascading data are checked. (The fileds I want to update are not in both tables) Table name= payForward Has 15 fields ie: ID, Name, MemID, Oct , Nov, Dec, etc Oct-Sep fields are yes/no type I want to "select" a field (Oct-Sep) via a query parameter and repalce "yes" with "no". Here is my query: UPDATE payForward SET [Enter month]=No The messages I get is 'operation must use an updateable query' Wh...

converting FILETIME into two DWORDS
Hi, i want to extract date and time from a FILETIME structure into two dwords. So that the two dwords contain the date and time info in the same way in which NTFS store the file creation, modification and access date. Thanks. ...

How do I combine two worksheets into one graph
I have two worksheets on an Excel spreadsheet. I would like to take both worksheets and place in one graph. Any suggestions? Make a chart with data from one sheet. Then copy the data on the other sheet, select the chart, and use Paste Special from the Edit menu to add the copied data as New Series. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ McPowerUser wrote: > I have two worksheets on an Excel spreadsheet. I would like to take both > worksheets and place in one graph. Any suggestions? ...

comparing numerically two documents
Hi all, I need to compare the text of document A with the text of document B with some Word functionality, plugin or tool that tells me how much the text in the two docs is different. Some kind of reporting feature that tells you something like "The text of document B is X % (percent) different from the text of document A" Is anythig like this available? thanks ...

How do I lock a chart so it will not update?
That's the question. I have my data in Excel and the chart in Excel but not all the data cells are used. Everytimg I open the chart it wants to update and I want it to stay the same. Any ideas on how to lock the chart? Hi Just a few ideas: You could lock the cells that are shown in the chart. Or you could copy the cells and paste as values (assuming formulas were used that update when other cells change). -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Locking a Chart" wrote: > That's the question. I have my data in Excel and the chart in ...