How to change worksheet that exports Data to current worksheet with button?

[Giuseppe Jordão]

Greetings, everyone

I'm starting with excel, and I have a workbook with 3 initial pages(max,medium and min),
which are identical in structure, but with different values, followed by 100 pages, also with different values and same structure.
There are columns in all of these 100 pages that get their values directly from the first page (max).
However, I would like to add a button with a macros that would automatically replace page 1 in this procedure with
page 2, and another button to page 3. And a third button to return to feeding of the 100 sheets with page 1.

For example, cell Di has the formula "='max'!Ci", for i in range 1 to 100. I click the button "min" and it turns the formula to "='min'!Ci".

Sorry for the long text.

Thanks in advance!

[bmouse]

A simple change from "min" to "max" could be achieved by recording a macro. All you have to do is perform a standard Ctrl+H Find & Replace, click to look into Formulas not Values. After recording you can draw a button and assign the macro to it.

[Giuseppe Jordão]

Thank you for the fast response, and your solution is simple and fast, but that requires 6 buttons and 6 macros(6 recordings):

1 - change from min to max
2 - change from min to medium
3 - change from medium to max
4 - change from medium to min
5 - change from max to min
6 - change from max to medium

However, I'm already pleased with your solution!

[bmouse]

Maybe you don't need a macro at all. Do you want to be able to switch these things back and forth or is it just a one time change?

[Giuseppe Jordão]

Back and forth, it is my main issue :P

Click "max" button and all values in all sheets are now fed by the max sheet
Click "medium" button and all values in all sheets are now fed by the medium sheet
Click "min" button and all values in all sheets are now fed by the min sheet

[bmouse]

See if you can work with this.
Here is the entire code, 3 separate macros, 1 for each button.

EndNumber

Please Login or Register  to view this content.

[Giuseppe Jordão]

That is awesome! :D
Although the code is writen for just one column, I can easily extend it to the other columns.

Thank you so much! Great work!

[bmouse]

Good luck

[Giuseppe Jordão]

Sorry, I forgot to ask:
What code lines do I have to add in order to select all the 100 sheets afeter the first three sheets?

My final goal is to do this procedure to all 100 sheets simultaneously.

Thanks!

[bmouse]

I wish I knew, I can't write macros that run accross an entire workbook; single sheet only
This line of code does the actual changing, and I have no idea how to make it do stuff in other sheets simultaneously.

Cells(StartNumber, "A").Value = "=max!A" & StartNumber

StartNumber is just the row number and "A" is the column letter. EndNumber is the number of times this procedure will be performed, in other words it is the total number of rows you have. The example macro only performs itself 10 times.


EDIT:

I found this command for switching sheets:

Worksheets("Sheet1").Activate

and this for looping through the entire workbook:

Please Login or Register  to view this content.

[Giuseppe Jordão]

I tried to use this WorksheetLoop in mine, but it is quite slow...
If someday you find a code that can run many sheets
and allow to perform many actions in each, please tell me.
Your help has been precious!
Thank you so much

[bmouse]

Thanks, good luck