Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How do I create a live copy of a worksheet in a secondary workbook

0

I need to split a master workbook up into 4 different workbooks, however they need to be live copies so I only have to update the master workbook and the satelite workbooks would then "hopefully" update from that information since 1 workbook needs to contain worksheets A,B,C,D & F, the 2nd needs to contain worksheets A,B,C,G and H, the 3rd workbook needs to contain worksheets A,B,C, E, H, I and the 4th workbook needs to contain worksheets A,B,C, and J (overlapping worksheets).  I can save the workbooks in my personal read only file that the other managers can access (and then not change), but none of them want to look at the whole huge master workbook as that will eventually contain a worksheet for the entire department plus 23 more for each division.  I'm not fabulous with code as I haven't had to write any for a few years, so any direction is wonderful!

Thank you in advance - Carrie

excelworkbook-settingsformatting
MS Office Version: 14061125000
posted by: Carrie on Mon Sep 11, 2017 at 2:08 pm
Answer
Discuss

Answers

0

Perhaps you could have a MASTER Tab. Depending on if/how you are sorting you could use Filters for the 4 different sheets  OR  have the Master tab, create formulas in subsequent sheets that 'pulls' the columns you want. So Sheet1 is Master; Sheet2 pulls from Master your columns A,B,C,G and H etc.

posted by: queue (rep: 467) on Mon Sep 11, 2017 at 2:23 pm
Discuss
0

In data processing you don't keep any "live copies" because you never keep copies. That is because the more copies you have the more work there is to maintain them. Therefore you should plan on having only your Master workbook. No copies.

From the Master workbook you create throw-away "views", to wit, your 4 workbooks. They should be created from scratch from the Master, perhaps at the click of a button. VBA can do that. It's very fast. You never look for what has changed and update. Throw away what you have and create new.

If you don't like to press a button you can automate it to run on every Save or even every change. If you go as far as that there would be no more discernible difference between what you had in mind and what I suggest. But there is a practical difference in that you don't have to maintain the 4 workbooks. You only maintain the Master.

Implementation is neither very voluminous nor difficult. But to have a mockup of the Master is essential.

posted by: Variatus (rep: 4889) on Mon Sep 11, 2017 at 8:53 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login