Reflect all activity happening on Master Excel sheet to the Copy of same Master excel sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-2708990%22%20slang%3D%22en-US%22%3EReflect%20all%20activity%20happening%20on%20Master%20Excel%20sheet%20to%20the%20Copy%20of%20same%20Master%20excel%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2708990%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20excel%20workbook%20that%20contains%20a%20%22Master%22%20sheet.%3C%2FP%3E%3CP%3EI%20want%20to%20simply%20copy%20the%20data%20from%20the%20%22Master%22%20Sheet%20and%20create%20a%20copy%20of%20the%20%22Master%22%20Sheet.%20I%20was%20able%20to%20achieve%20that%20easily%20using%20conditional%20formatting%20and%20cell%20references.%3C%2FP%3E%3CP%3EHowever%2C%20the%20main%20issue%20is%20the%20row%20numbers%20are%20needed%20to%20appear%20keeps%20on%20changing%2C%20some%20rows%20are%20needed%20to%20be%20hidden%20and%20those%20are%20not%20fixed.%3C%2FP%3E%3CP%3ESuppose%20for%20Week%201%2C%20in%20the%20%22Master%22%20sheet%20I%20need%20row%20number%20%224%3A10%22%20needs%20to%20be%20hidden%20or%20delete%20any%20rows%20or%20columns%2C%20and%20I%20can%20simple%20copy%20the%20data%20and%20the%20formatting%20to%20achieve%20that%3C%2FP%3E%3CP%3EBut%20for%20Week%202%2C%20I%20need%20to%20show%20from%20%22Master%22%20sheet%20row%20number%20%228%3A10%22%20but%20now%20hide%20rows%20%221%3A7%22%2C%20this%20should%20reflect%20in%20the%20copy%20of%20the%20sheet.%3C%2FP%3E%3CP%3ESo%20in%20simple%20words%2C%20whatever%20action%20is%20being%20performed%20in%20the%20%22Master%22%20sheet%20should%20reflect%20in%20the%20copy%20of%20that%20sheet.%3C%2FP%3E%3CP%3EI%20cannot%20afford%20to%20do%20this%20manually%2C%20since%20there%20are%20a%20lot%20of%20workbooks%20that%20need%20the%20same%20type%20of%20action.%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20if%20there%20is%20any%20macro%20that%20can%20do%20this%20or%20any%20excel%20function.%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2708990%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2709259%22%20slang%3D%22en-US%22%3ERe%3A%20Reflect%20all%20activity%20happening%20on%20Master%20Excel%20sheet%20to%20the%20Copy%20of%20same%20Master%20excel%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2709259%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F724985%22%20target%3D%22_blank%22%3E%40rmghosh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20a%20bit%20difficult%20to%20follow%20your%20description%20without%20an%20actual%20spreadsheet%20in%20front%20of%20us.%20Is%20it%20possible%20for%20you%20to%20post%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20said%2C%20although%20what%20you're%20asking%20may%20indeed%20be%20possible%20via%20VBA%20or%20macro--I%20avoid%20them%20unless%20absolutely%20necessary%20personally%2C%20in%20favor%20of%20functions%20that%20don't%20need%20to%20be%20debugged.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20absence%20of%20a%20sample%20of%20your%20files%2C%20let%20me%20refer%20you%20to%20the%20recently%20released%20FILTER%20function%2C%20that%20may%20be%20able%20to%20do%20what%20you're%20asking.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%3C%2FA%3E.%20This%20does%20require%20the%20most%20recent%20version%20of%20Excel%2C%20but%20you're%20able%20to%20draw%20selected%20rows%20of%20a%20table%20from%20one%20sheet%20to%20another%20using%20various%20criteria.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have an excel workbook that contains a "Master" sheet.

I want to simply copy the data from the "Master" Sheet and create a copy of the "Master" Sheet. I was able to achieve that easily using conditional formatting and cell references.

However, the main issue is the row numbers are needed to appear keeps on changing, some rows are needed to be hidden and those are not fixed.

Suppose for Week 1, in the "Master" sheet I need row number "4:10" needs to be hidden or delete any rows or columns, and I can simple copy the data and the formatting to achieve that

But for Week 2, I need to show from "Master" sheet row number "8:10" but now hide rows "1:7", this should reflect in the copy of the sheet.

So in simple words, whatever action is being performed in the "Master" sheet should reflect in the copy of that sheet.

I cannot afford to do this manually, since there are a lot of workbooks that need the same type of action.

Please let me know if there is any macro that can do this or any excel function.

Thank you.

1 Reply

@rmghosh 

 

It is a bit difficult to follow your description without an actual spreadsheet in front of us. Is it possible for you to post it?

 

That said, although what you're asking may indeed be possible via VBA or macro--I avoid them unless absolutely necessary personally, in favor of functions that don't need to be debugged.

 

In the absence of a sample of your files, let me refer you to the recently released FILTER function, that may be able to do what you're asking. https://www.youtube.com/watch?v=9I9DtFOVPIg. This does require the most recent version of Excel, but you're able to draw selected rows of a table from one sheet to another using various criteria.

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...