VBA to collect data from different files to one file on different tabs(worksheets)

Giggs1991

New Member
Joined
Mar 17, 2019
Messages
44
Hi All,

I am trying to get a vba code to copy information from different files to one destination file but on different worksheets on the destination file.

The destination file is called "Master data file ".

The master file has worksheets named ""Sheet1","Project1" , "Project2", "Project3", "Project4", "Project5"............."Project13"

Sheet 1 on "The master data file " has the following list of file destinations from A2 onwards.

C:\Users\user12\Downloads\Budget report\project1\Project1.xlsm
C:\Users\user12\Downloads\Budget report\project2\Project2.xlsm
C:\Users\user12\Downloads\Budget report\project3\Project3.xlsm
C:\Users\user12\Downloads\Budget report\project4\Project4.xlsm
C:\Users\user12\Downloads\Budget report\project5\Project5.xlsm
C:\Users\user12\Downloads\Budget report\project6\Project6.xlsm
C:\Users\user12\Downloads\Budget report\project7\Project7.xlsm
C:\Users\user12\Downloads\Budget report\project8\Project8.xlsm
C:\Users\user12\Downloads\Budget report\project9\Project9.xlsm
C:\Users\user12\Downloads\Budget report\project10\Project10.xlsm
C:\Users\user12\Downloads\Budget report\project11\Project11.xlsm
C:\Users\user12\Downloads\Budget report\project12\Project12.xlsm
C:\Users\user12\Downloads\Budget report\project13\Project13.xlsm


Each of these files mentioned above have a worksheet named "Budget". And in each "budget" tab there is a cell named "FY21 DATA" .
When the VBA code runs, for example, it should go to the file "C:\Users\user12\Downloads\Budget report\project1\Project1.xlsm" and then go to it's "Budget" tab , find the cell named" FY21 DATA" , copy and paste it to the "Project1" worksheet in the "master data file ". The same should happen for all projects till project 13.

I have uploaded an image of what the tabs in Master data file looks "

Untitled.png
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
304
Office Version
  1. 365
Platform
  1. Windows
Is the "FY21 DATA" cell in each workbook at the same address on the Budget worksheet? If so, what's the address?

I ask because when you say "the cell named 'FY21 DATA' ", this suggests that you are referring to a named range, but named ranges can't have spaces in them so I am guessing (?) you are saying that the cell is 'named' this in the colloquial sense.
 

Giggs1991

New Member
Joined
Mar 17, 2019
Messages
44
Is the "FY21 DATA" cell in each workbook at the same address on the Budget worksheet? If so, what's the address?

I ask because when you say "the cell named 'FY21 DATA' ", this suggests that you are referring to a named range, but named ranges can't have spaces in them so I am guessing (?) you are saying that the cell is 'named' this in the colloquial sense.
The "FY21 DATA" is in a different address in each workbook. But it's in the same worksheet named"Budget" in each workbook. It is not a named range.
 

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
304
Office Version
  1. 365
Platform
  1. Windows
Can you please provide some samples worksheets so we can try and work out how to go about finding it programmatically. Thank you.
 

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
304
Office Version
  1. 365
Platform
  1. Windows
I don't think there is, but depending on what the spreadsheet looks like, pictures would do. Or XL2BB (as below).

Book47
ABCD
1
2
3
4
5
6
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,123,231
Messages
5,600,427
Members
414,384
Latest member
joehalks

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