Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: code to work out from 2 workbooks
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default code to work out from 2 workbooks

    Hi i hope you can help, i have 2 workbooks (Book13 and Book14) these both have the same data, for example in cell C12 is the number 13 in both workbooks, but if someone reduces this number to 11 in either of the workbooks i want the other workbook to automatically update the cell to 11, i dont want to mirrow the workbook as i want them both live. please can you help with the code?

  2. #2
    Board Regular gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,894
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: code to work out from 2 workbooks

    Paste this code in each worksheet of Book13 that y ou want it to work on.

    Paste it in the same sheets on Book14 but change "Book14" to "Book13"

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        
        'this sub requires both workbooks to be open
    
    
        Application.EnableEvents = False
        'This code goes in Book13, amend for book 14
        Workbooks("Book14").Sheets(Me.Name).Range(Target.Address) = Target
        Application.EnableEvents = True
        
    End Sub
    Last edited by gallen; Aug 22nd, 2019 at 06:14 AM.

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


  3. #3
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: code to work out from 2 workbooks

    Hi thanks how do i add it as a link where the book is located ie C:\Users\s21375\Desktop\Book13.xlsm

  4. #4
    Board Regular gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,894
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: code to work out from 2 workbooks

    You don't need to if the book is open. Just ensure both books are open. If you require code to check if the workbook is open, then if not ,open it, that's a different set coding altogether. Do you need to be able to do it. even if workbook is closed?

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


  5. #5
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: code to work out from 2 workbooks

    Hi yes please if workbook is closed as well.
    i did try the code below which worked fine in one workbook but when i added the code to the other workbook it crashes for some reason (i just changed the book13 to book14).
    Code:
      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:\Users\s21375\Desktop\Book13.xlsm"
      Workbooks(s1).Sheets(s2).Range(addy).Copy ActiveWorkbook.Sheets(s2).Range(addy)
      ActiveWorkbook.Save
       
      End Sub
      

  6. #6
    Board Regular gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,894
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: code to work out from 2 workbooks

    This works. Pate in to all sheets you need "Mirroring"

    Amend the filename line (highlighted with ***) to suit.

    Please note that this code closes the workbook if it was originally closed. Remove the "wb.close" line if you want it to stay open


    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        'This code goes in Book13, amend for book 14
        
        
        'this sub requires both workbooks to be open
        Dim wb As Workbook
        Dim spath As String, sFileName As String
        Dim bOpen As Boolean 'remember if workbook was open or not
        
        spath = "C:\Users\s21375\Desktop\"
        
        ' *** This line needs changing for Book 13
        sFileName = "Book14.xlsm"
        
        On Error Resume Next
        Set wb = Workbooks(sFileName)
        If wb Is Nothing Then
            Set wb = Workbooks.Open(spath & sFileName)
            If wb Is Nothing Then
                MsgBox "File can not be opened", vbCritical
                Exit Sub
            End If
        Else
            bOpen = True
        End If
        On Error GoTo 0
        
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        
        wb.Sheets(Me.Name).Range(Target.Address) = Target
        If bOpen = False Then
            wb.Save
            wb.Close
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        
    End Sub

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


  7. #7
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: code to work out from 2 workbooks

    this works great thank you

  8. #8
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: code to work out from 2 workbooks

    Hi this code works great, but how can i make it work if both workbooks arent open, it works great with both workbooks open, but it doesnt update the other workbook if it is closed.

  9. #9
    Board Regular gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,894
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: code to work out from 2 workbooks

    The code works if at least one of the sheets is open.

    how can i make it work if both workbooks arent open
    Now that is confusing. Surely at least one of them has to be open for the user to change it, to trigger changing the other??

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


  10. #10
    Board Regular
    Join Date
    Feb 2018
    Location
    Midlands, UK
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: code to work out from 2 workbooks

    ohh sorry, i worded that wrong, i meant to say if only one spreadsheet is open, then it doesnt update the other spreadsheet, it works great if they are both open, but not if just the one. sorry

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •