Sign in
 
Home20132010Other VersionsLibraryForumsGallery
TechCenter >
> 
> 
Copy cells from one workbook to another automatically.

Answered Copy cells from one workbook to another automatically.

  • Thursday, November 03, 2011 11:37 PM
     
     
    Sign In to Vote
    0

    Workbook 1 contains a set of data, Workbook 2 contains a different set of data but there is common data in both.  Instead of updating both with the common data, how do I copy the contents of the common cells from Workbook 1 to Workbook 2?  It would be an added feature if this could be done with a single keyclick - keep in mind I have no experience with macros or scripts.  Thanks in advance.

     

All Replies

  • Friday, November 04, 2011 12:21 AM
     
     
    Sign In to Vote
    0
    If the data is common to both workbooks, there is no need to copy it.  "common" means its already there!
    gsnu201109
     
  • Friday, November 04, 2011 1:12 AM
     
     
    Sign In to Vote
    0
    In article <b5e7ec15-980f-4ade-b133-ad379fdeb567
    @communitybridge.codeplex.com>, Gary's Student [MVP] says...
    >
    > If the data is common to both workbooks, there is no need to copy it.  
    > "common" means its already there!
     
    I guess he means some data in both books have to be sychronized.
    e.g. for both book having a tab "Common" with identical content.
     
    I both books can be e.g. an event macro,
    that in case of updating of Common tab
    would copy the content to the other book.
     
    Alternative solution could be the 3rd book
    with the only common data copy,
    that would be referenced by the both books.
     
    --
    Poutnik
     
     
  • Friday, November 04, 2011 1:29 AM
     
     Answered
    Sign In to Vote
    0

    I see what you mean.  Say we have two workbooks, alpha.xlsm and beta.xls.  Say both have Sheet1 which should have common data and the alpha workbook is the one updated manually and the beta sheet needs to be updated accordingly.

     

    Include the following event macro in Sheet1 of the alpha workbook:

     

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim s1 As String, s2 As String, addy As String
    s1 = ThisWorkbook.Name
    s2 = ActiveSheet.Name
    addy = Target.Address
    ActiveWorkbook.FollowHyperlink Address:="file:///C:\TestFolder\beta.xlsx"
    Workbooks(s1).Sheets(s2).Range(addy).Copy ActiveWorkbook.Sheets(s2).Range(addy)
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    End Sub

    The hyperlink is an easy way to get beta opened if it is not.  The copy is then done workbook-to-workbook.  The Save/Close are done to insure that updates are not lost if "something bad" happens.

    gsnu201109
     
  • Friday, November 04, 2011 4:07 AM
     
     
    Sign In to Vote
    0
    Gary's Student/Poutnik - looks promising.  I'll try my hand at building the macro and update on my efforts.  Thank you for taking the time, much appreciated. 
     
  • Friday, November 04, 2011 6:55 AM
     
     
    Sign In to Vote
    0
    In article <6758daa5-8283-4079-a527-d67306ece4d5
    @communitybridge.codeplex.com>, 2manyquestions says...
    >
    > Workbook 1 contains a set of data, Workbook 2 contains a different set of 
    > data but there is common data in both.  Instead of updating both with  the
    > common data, how do I copy the contents of the common cells from Workbook  1
    > to Workbook 2?  It would be an added feature if this could be done with  a
    > single keyclick - keep in mind I have no experience with macros or script s. 
    >  Thanks in advance.
     
    There is also another option,
    if the books stay together, being available each to other.
     
    You can use as cell value in one workbook
    the reference to a cell in the other workbook.
     
    It is usable in case one book is a master,
    and the other would just mirror the common value by formula.
     
    --
    Poutnik
     
     
  • Friday, November 04, 2011 7:06 AM
     
      Has Code
    Sign In to Vote
    0
    This solution is elegant. If you put the same to the other book, replacing the filename as C:\TestFolder\alfa.xlsx, then it does not matter in what book you do the change. Things would get complicated if not all the sheet is to be shared. In such a case referencing formula could be better, or such an event macro would anumarate affected cells only, like ...
    sRange = "a1"
    Workbooks(s1).Sheets(s2).Range(sRange).Copy ActiveWorkbook.Sheets(s2).Range(sRange)
    sRange = "a2:c5"
    Workbooks(s1).Sheets(s2).Range(sRange).Copy ActiveWorkbook.Sheets(s2).Range(sRange)
    sRange = "D:D"
    Workbooks(s1).Sheets(s2).Range(sRange).Copy ActiveWorkbook.Sheets(s2).Range(sRange)
    • Edited by Poutnik Friday, November 04, 2011 7:08 AM
    •  
     
  • Friday, November 04, 2011 10:55 AM
     
     Answered
    Sign In to Vote
    0


    Because it is worksheet code, it is very easy to install and automatic to use:

       1. right-click the tab name near the bottom of the Excel window
       2. select View Code - this brings up a VBE window
       3. paste the stuff in and close the VBE window

    If you have any concerns, first try it on a trial worksheet.

    If you save the workbook, the macro will be saved with it.


    To remove the macro:

       1. bring up the VBE windows as above
       2. clear the code out
       3. close the VBE window

    To learn more about macros in general, see:

    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    To learn more about Event Macros (worksheet code), see:

    http://www.mvps.org/dmcritchie/excel/event.htm

    gsnu201109