[SOLVED] Automatic macro to send changing values from one sheet to many other sheets.

I have a 32 sheet workbook. Each day I change the date and cell E1 on sheet 1. When the date on sheet 2, 3, 4, 5.... match the date on sheet 1 then cell E1=MasterE1.
What I would like to do is make E2=E1 on that date, so when I change the date and E1 on the master cell E2 will still show what E1"s date from the previous day.
is this possible?

1-1-11 Master(A1). E1=3.33 date cell A1
sheet 1 E1 & E2 =3.33 date cell A1

1-2-11 master. E1=3.40
sheet 1 E1 will be blank E2=3.33
sheet 2 E1 & E2 = 3.40
and so on

Yes it is possible, from what you are saying. Can you post a dummy workbook with sensitive info taken out, to see exactly what you are after?

Do you mean?

=IF(A1=master!A1,master!E1,"") in each sheet.

You can select all the sheets except for the master, by selecting sheet1, holding shift key, then selecting penultimate sheet. Then enter formula in your cell of choice, the right click any tab and select Ungroup sheets

Hi Simpson,

I believe VBA can do what you are looking for. I don't quite understand the exact problem yet but here are some hints.
If your master sheet is the last sheet on the right you can select it using "Worksheets(Worksheets.Count)"
Your VBA code would cycle through each sheet using some code like:

Please Login or Register  to view this content.

The above code will run down your worksheets looking at each sheet and doing what you code it to do.
See http://msdn.microsoft.com/en-us/libr...ffice.11).aspx or
http://dmcritchie.mvps.org/excel/sheets.htm
I hope this helps.

thanks again, but its not changing each sheet i'm after here.
I'm trying to get E1 on each sheet to show the value from master E1 on specific dates.
If you go to my sample workbook, look at sheet 1 E1 = 3.33. because master E1 = 3.33 and dates match.
then, change the master date to 12/9/2011 and E1 to 3.40.
sheet 2 now shows E1 = 3.40. thats good. but now sheet 1 cell E1 is blank. I need it to keep the 3.33 that was in there before the price/date change.

sheet 1 cell E1 formula something like.....
=masterE1 on (date from A1)

This macro will do what you want:

Please Login or Register  to view this content.

This macro goes into the "master" sheet module, right-click the sheet tab, select VIEW CODE, then paste in the code above. Save the workbook as a .xlsm to keep the macro active.

Any changes on the Master sheet will trigger it. The first sheet found that has a date matching Master!A1 will receive the value from E1.

Originally Posted by simpson

is it possible to modify the code to save data from E1:E6 instead of just E1?

Like so:

Please Login or Register  to view this content.

Now you have two examples. Projects like this always tend to "balloon" in requirements, so read through both of these examples line by line and make sure you understand what each line of code is doing, these are simple macros and it's important so you can expand the macro later. Let's talk about any lines of code you want further explanation on if you don't see what they do, ok?

Then, as we continue forward, be sure to post back showing the code changes you've tried that didn't work for your new requirements, sound good?

Glad it works for you!

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

Maybe:

Automatic Macro to send values from one sheet to many other sheets.