Hello Board,
I have the following question on VBA.
I have a large excel with 100 different tabs which currently includes many hardcodes. The tabs have the exact same structure which is helpful so you can always pull up the same value from the same place. I have created a new tab which will be my transfer tab and will consolidate all the different cells there and then these cells are going to be linked to each individual other tab.
Please see original excel example here :
Please see solution output of the excel here of what the excel file should look like at the end of the exercise:
I want to use a VBA formula that does the following:
1. Copy paste all values that are in I4 in tabs that are between tab "R>>>" and "<<<L" and paste these values in tab "Master" one under the other by putting the tab name in A2 and the actual value in B2 downwards for all the tabs (please have a look at solution excel)
2. After it has copied all values, re-link all those values as references in the original tab where it picked that value from (please see solution excel, cell I4 is now a link to the "Master" tab
3. Replicate this for cells L10 and cells D17 in those same tabs and paste values in "Master" tab and re-paste links to original tabs (please see solution)
I am quite new to VBA so I'd appreciate if you can tell me exactly what part I need to change in the VBA code to replicate this in my actual excel multiple times to pick up a lot of values.
Thank you!
I have the following question on VBA.
I have a large excel with 100 different tabs which currently includes many hardcodes. The tabs have the exact same structure which is helpful so you can always pull up the same value from the same place. I have created a new tab which will be my transfer tab and will consolidate all the different cells there and then these cells are going to be linked to each individual other tab.
Please see original excel example here :
Excel -Macro Problem.xlsm
Master Tab Name ,Cell 1 value,Tab name,Cell 2 value Tab Name ,Cell 3 value
docs.google.com
Please see solution output of the excel here of what the excel file should look like at the end of the exercise:
Excel -Macro Answer.xlsm
Master Tab Name ,Cell 1 value,Tab name,Cell 2 value Tab1,234324,Tab1,151 Tab2,2411,Tab2,12 Tab3,45433,Tab3,5 Tab4,123,Tab4,358 Tab5,1552,Tab5,713 Tab6,123123,Tab6,144 Tab7,6542,Tab7,1574 Tab Name ,Cell 3 value Tab1,123421 Tab2,13435645 Tab3,16555512365 Tab4,7568562 Tab5,3451231 Tab6,12475686 Ta...
docs.google.com
I want to use a VBA formula that does the following:
1. Copy paste all values that are in I4 in tabs that are between tab "R>>>" and "<<<L" and paste these values in tab "Master" one under the other by putting the tab name in A2 and the actual value in B2 downwards for all the tabs (please have a look at solution excel)
2. After it has copied all values, re-link all those values as references in the original tab where it picked that value from (please see solution excel, cell I4 is now a link to the "Master" tab
3. Replicate this for cells L10 and cells D17 in those same tabs and paste values in "Master" tab and re-paste links to original tabs (please see solution)
I am quite new to VBA so I'd appreciate if you can tell me exactly what part I need to change in the VBA code to replicate this in my actual excel multiple times to pick up a lot of values.
Thank you!