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
In (sheet tab "shipping") sheet module
In (sheet tab "file1") sheet module
In standard module
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
Bookmarks