VBA Question to Replicate Copy and Paste Links in Excel

Lowbob131

New Member
Joined
Feb 3, 2022
Messages
10
Office Version
  1. 365
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!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top