+ Reply to Thread
Results 1 to 3 of 3

Two Workbooks - One Connected Sheet

  1. #1
    Registered User
    Join Date
    04-02-2018
    Location
    Vermont, USA
    MS-Off Ver
    Microsoft Office 365 Business
    Posts
    8

    Two Workbooks - One Connected Sheet

    Hi there!
    I have two workbooks with different purposes, each have a shipping sheet. I would like for the shipping sheet to be exactly the same in both workbooks and any update in either sheet, update the other workbook.
    Example below:
    Workbook 1:
    Shipping sheet:
    Adds shipment of shoes. Shoes cost 45.50

    Workbook 2:
    Shipping sheet:
    Shipment of shoes and cost are updated.
    And vice versa.

    I hope my objective is clear, if anyone can offer any guidance that would be appreciated.
    Thank you!

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Two Workbooks - One Connected Sheet

    I think you'll need VBA if you want to overwrite data on each workbook

  3. #3
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Two Workbooks - One Connected Sheet

    Regardless of controls in place the 2 sheets will go out of sync at some point
    - so keep things simple and run only one copy of the sheet
    - there are a several ways of achieving this and making it very easy for the user

    Suggestion1 - run 3 workbooks but make them behave like single workbook
    - clicking on sheet tabs in one workbook takes user to the correct tab in the other workbook
    - if the file is not open, it is opened automatically

    To test:
    - save the attached workbooks to the same folder
    - open "shipping.xlsm"
    - click on tab "File1"
    - user now in wbS1 (opens automatically)
    - click on tab "File2"
    - user now in wbS2 (opens automatically)
    - user now free to hop back and forth as if in a single workbook

    Example VBA from wbS2 - the other files contain almost identical VBA
    In ThisWorkBook module
    Please Login or Register  to view this content.
    In (sheet tab "shipping") sheet module
    Please Login or Register  to view this content.
    In (sheet tab "file1") sheet module
    Please Login or Register  to view this content.
    In standard module
    Please Login or Register  to view this content.

    Adapting it to your needs
    This is a staring point (and kept very basic) which could be modified in many ways to make it work well for you
    - eg "shipping" could be a sheet in either of the 2 workbooks and use the same trick as above
    If you like the suggestion
    - after you have had an opportunity to think about how it could work for you
    - come back and ask for more specific tailored help

    Other Uses
    I have 8 files that I use regularly throughout the day
    - each file is single sheet
    - they bear no relationship to each other but I am forever opening and closing them
    - so each of the workbooks has 7 additional tabs (named single character alpha)
    - tabs are always visible unlike a button
    - could get button to follow the cursor, but why bother?
    - did not want 8 different shortcuts
    - has low "system" overhead
    - results in easy access to my most used files
    Attached Files Attached Files
    Last edited by kev_; 04-14-2018 at 07:17 AM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Refresh Pivot Table Connected to Protected Sheet
    By tryingtoexcelatexcel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-25-2017, 10:42 PM
  2. [SOLVED] Is it possible to have a connected summary sheet to brake down sheets
    By Redfang87 in forum Excel General
    Replies: 3
    Last Post: 11-20-2016, 06:51 AM
  3. Rename sheet in workbooks(2) changes workbooks(1) saved status
    By aussieboykie in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-12-2014, 06:04 PM
  4. Selective columns in connected workbooks
    By Steffen_DK in forum Excel General
    Replies: 1
    Last Post: 03-20-2014, 05:39 AM
  5. [SOLVED] Macros connected to a hidden sheet
    By Saturn in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-14-2014, 12:42 PM
  6. [SOLVED] Consolidate Data from 1 Range on 1 Sheet in Multiple Workbooks to Master Workbook Sheet
    By Jennasis in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-10-2013, 06:11 AM
  7. Multiple workbooks need to be connected and update together
    By GingerLeake in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-28-2013, 08:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1