VBA to autofill cells with data from other sheets

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
146
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I tried to enter formulas in each cell and I am getting so confused.
Each column is getting the value from another workbook that contains two sheets of data using a formula such as:
Excel Formula:
='G:\Documents\[filename.xlsx]Sheet2'!$C1
The problem is, every workbook is different so entering formulas each time is really time consuming.
What is the best way to do this?
I have named S3 as the Workbook that I am trying to autofill the cells with the data from Workbook.xlsx Sheets1 (S1) and Sheet2 (S2).
Still with me?:)
OK, so here are the requirements.
S3 B7 = S1 B7
S3 G7 = S2 C1
S3 I7 = S1 B7
S3 J7 = S2 C2
S3 P7 = S2 G4
S3 Q7 = S2 G5
S3 U7 = S2 G7
S3 V7 = S2 H7
S3 AB7 = S2 I20
S3 AC7 = S2 J20

How do I do this?
I am still too new to figure out how to do this.
Help please?
Thanking you in advance!!!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,717
Office Version
  1. 365
Platform
  1. Windows
I am not sure where the confusion lies. Wouldn't you just repeat the process you already have?

So
S3 B7 = S1 B7
would just be:
Excel Formula:
='G:\Documents\[filename.xlsx]Sheet1'!$B7

and
S3 B7 = S2 C1
would just be:
Excel Formula:
='G:\Documents\[filename.xlsx]Sheet2'!$C1
etc.

If you need to do it in VBA, you can just turn on the Macro Recorder, and record yourself manually doing this, and it will give you the code that you need.
Just note that the Macro Recorder is very literal (it records all cell selections, which are not necessary), and you can clean it up a bit.

So each "set" of commands to enter the the formula like this:
VBA Code:
    Range("B7").Select
    ActiveCell.FormulaR1C1 = "='G:\Documents\[filename.xlsx]Sheet1'!RC2"
can be combined/reduced to this:
VBA Code:
    Range("B7").FormulaR1C1 = "='G:\Documents\[filename.xlsx]Sheet1'!RC2"
 

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
146
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Joe4, thanks for the suggestion with the macro recording. I tried your suggestion but it's not working for me. I'm a noob so maybe I'm not doing it properly but I've given up after a dozen attempts. Not sure where I am going wrong/ I've never recorded a macro before.
Is there another way of doing this?
As I had mentioned, this is a new file every week, so manually re-entering the formulas into every cell each time, is so time consuming.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,717
Office Version
  1. 365
Platform
  1. Windows
Can you post the code that you recorded, and we can help you fix it up?
We may have to add some coding to make it dynamic so that it will work for any workbook.
Did you say that the file name is located in cell S3?
 

Watch MrExcel Video

Forum statistics

Threads
1,128,020
Messages
5,628,182
Members
416,299
Latest member
arunvistas

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