Updating a set of worksheets from a single master.

[Getafix1066]

Hi, I have an excel problem and I wonder if anyone here can help.

I'll start by describing what I currently do, and then I'll get onto what I want to do.

I have a large set of data (several thousand rows) that is generated every day from an external system. It arrives as a set of coma-separated variables. I've created a blank worksheet that I refer to as my 'master template', it's got all the charts, formulas and summaries that I need to analize each days data. Each day I paste my new data into one corner of the worksheet, and all the charts and statistics for the data is automatically populated. I then copy this to a new worksheet called (for example) May16. I refer to these worksheets as my 'clones'

This all works fine, at the end of the month I end up with a spreadsheet that contains one worksheet for each day of the month - which is exactly what I want.

The problem is this, every now and again the requirements for my master template worksheet change, and the way things work at the moment, this means I have to do identical edits on each of my daily worksheets.

Is there any way I can automate this? I'd really like to just update my master template and then for the clones to automatically pick up the changes. Is this possible? Can I have a master spreadsheet and have all the clones automatically copy anything than happens to the master. I'm not talking about one worksheet pick up a value from another worksheet (I know how to do that!) I'm talking about one worksheet picking up a whole new feature from the master worksheet, so (for example) if I need to add a new chart I can just add it to the master it will automatically appear on the clones.

Any suggestions?

Thanks

 

[L. Howard]

How about selecting Master and all the Clones that you want the change to affect and make the change on Master.

So if you have Master and 22 Clones, click on the Master sheet tab and then scroll to last Clone and hold Shift key down and select the last Clone tab.

Make the change on Master.

De-select all the selected Master/Clones and they all should have the new stuff.

Howard

 

[Roderick_E]

Maybe with some fancy VBA coding that somehow stores everything that exists on the master sheet and then if something has changed from the previous storage timestamp, it makes those same changes to all the clones.

 

[L. Howard]

If the All-Sheet selection method works for you, you could use this snippet, by Allen Wyatt, to do the selection of all visible sheets in the workbook.

Howard

Sub SelectSheets1()
    Dim mySheet As Object
    Sheets("Master").Activate

    For Each mySheet In Sheets
        With mySheet
            If .Visible = True Then .Select Replace:=False
        End With
    Next mySheet
    
End Sub

 

[Getafix1066]

ok, it seems I can highlight several worksheets at once and make changes to all of them at the same time. This seems to work for most of the simple changes I want to make. Thanks for the suggestions.


I was also wondering about an alternative idea, since each sheet is identical to each other except for the base data. Could I just create a set of worksheets that contain nothing but the base data, and then have a master worksheet which has a single cell in it in which I define which worksheet to get the data from?

That way when I want to change my master worksheet I can simple delete all the clones, make the change to my master, copy all the clones again, and update the single cell that tells them where to get the data from?

 

[L. Howard]

I am not following the alternative idea. In your first post you say your data comes into Master daily for analysis and then to a Clone to represents that day, then repeat for each day.

So, do you want the new incoming data to now go to the Clones as it arrives on a daily basis, (one clone for each day), and when you enter a "call Clone X" into a cell on Master, it retrieves the 1000's of rows of data from the chosen Clone into Master sheet where "...all the charts, formulas and summaries that I need to analize each days data." reside?

If so, then what would you do with the "calculated and refined and charted data" that now resides on Master sheet. Does it need to be stored somewhere, or is that data just for a quick look-see then dumped when another Clone's new data comes to Master?

Howard