How is this macro activated? Need to update.

[Malkier]

I have the below macro which compiles data from a number of sheets - whose sheetnames are listed on the sheet LoadList - to a MasterSheet. Every month I add a new sheet and add its name to the LoadList. It was originally written to update when one of the cells on one of the sheets in the LoadList was changed, but now this seems not to be happening when I edit one of the sheets that I have since the macro was written.

What I would like is to update the macro so that if ANY of the sheets are updated in the LoadList the Macro is activated.

Thanks!
Zac

Please Login or Register  to view this content.

[ozbod]

Try the worksheet change event on the LoadList worksheet

Please Login or Register  to view this content.

[Malkier]

Hi ozbod,

Thanks for your reply and your suggestion I think it might get me part the way there.

My concern is this.....my sheets are arranged as follows:

Master Sheet, Jul, Jun, May, Apr, Mar, Feb, Jan, LoadList

The above code is stored on the Master Sheet, and I want the code to run when any of the Monthly sheets (in bold) have a Worksheet change event - Does that make sense? The load list can be used to activate the macro.

Currently I must go back to one of the old sheets that existed when the macro was written, say the 'Feb' or 'Jan' one, and edit a cell in one of these sheets before the macro will run.

Interestingly, when I do this it pulls information from ALL sheets perfectly, so it is just an activation problem.

Any ideas?

[Alf]

Perhaps this modification to ozbods code will do what you wish? To get it to work right click on "ThisWorkbook" -> "View Code" and paste the code in new window. Not sure but you probably need to change "(General)" to "Workbook" in the dropdown list (top left macro window).

Please Login or Register  to view this content.

Alf

[Malkier]

Hi Alf - thanks for your reply!

I tried this but get the error "sub or function not defined"...

Any ideas?

Thanks in advance!

[Alf]

Did you change "(General)" to "Workbook" in the dropdown list (top left macro window)?

Aside from this and checking the name "CombinedSelected" is an excisting macro name I don't have any other ideas.

See link for supported workbook events

http://www.tutorialspoint.com/vba/vba_events.htm


Alf

[Malkier]

Thanks Alf,

When I clicked on ThisWorkbook and View Code and pasted the code the box at the top changed to "Workbook" automatically.

The CombinedSelected macro is stored on the "Master Sheet" sheet code - should it be in a separate module?

[Alf]

Well you could try and move the macro CombinedSelected to a separate and see if this helps.

But if I understands you right the code ozbod posted did work for you and then you had the CombinedSelected stored on the "Master Sheet" code so I'm not sure what happends.

Alf

[ozbod]

Malkier,

Sorry for the delay but have been away for the last week.

I read your original post to be that when any cell in "Loadlist" was changed then activate the macro. It seems that you are looking for when any cell in Jul, Jun,May etc is changed that you want the macro to activate. What is contained in the Loadlist sheet, is it just a reference to all the Jul,Jun etc sheets?

Firstly, I would recommend that you put the code in a separate module rather than master.

Alf's suggestion is a possible way to trigger this but you would need to restrict this so that any update to "Master List" is excluded from the call otherwise you may potentially end up in an endless loop. Question is, are you fundamentally trying to add to, or repopulate Master List based on the event.

Possibly try (in 'This Workbook'... Workbook)

Please Login or Register  to view this content.

Beware however, without testing based upon your structure, that changes (like formatting) may also trigger the call to the macro and depending on the size this may cause some aggravation.

Edit: I have just done a quick test and formatting changes do not seem to activate the macro, only when I change values (Excel 2010)

Set up a test workbook with a sheet called Master List + several other sheets (default will do) and in This Workbook have the code

Please Login or Register  to view this content.

.... and in a new Module have

Please Login or Register  to view this content.

From here change the values in any of the other sheets, then change the formatting and see if the value changes in Master List (please note change the format in column A of Master List to include seconds)