Macro to link cell value in multiple workbooks to one master sheet

ctish

New Member
Joined
Jan 28, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
I have a master sheet that we link the value of a cell from a summary sheet of individual workbooks. What we do now manually, is open each workbook, go to the master sheet in column L, press =, then go back to the workbook and select cell E45. Then close that workbook. Then open the next one in the folder and repeat. I have attached images of what my master sheet looks like as well as the first sheet in each individual workbook looks like. Hopefully i have explained this well enough. If not, don't hesitate to ask any extra questions. I am looking for a macro (or a simple function within excel) that will copy cell E45 from the individual workbooks and paste as a link to column L as it goes down the list. I need it to close each workbook too once it pulls the information. Thanks in advance!
 

Attachments

  • Master Sheet.JPG
    Master Sheet.JPG
    130 KB · Views: 7
  • Individual Workbook.JPG
    Individual Workbook.JPG
    79.4 KB · Views: 7

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

ctish

New Member
Joined
Jan 28, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
This works perfectly if I add this step before I change the tab names. All the tab names have to be the same in order for this to work as I needed. Is there any way to tweak the code a little so that if every tab is named something different for each work book that this process would still work?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,383
Messages
5,624,375
Members
416,025
Latest member
VN5968

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
Top