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

[Giggs1991]

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 "

 

[Dan_W]

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]

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]

Can you please provide some samples worksheets so we can try and work out how to go about finding it programmatically. Thank you.

 

[Giggs1991]

Can you please provide some samples worksheets so we can try and work out how to go about finding it programmatically. Thank you.

is there a way to upload excel files here?

 

[Dan_W]

I don't think there is, but depending on what the spreadsheet looks like, pictures would do. Or XL2BB (as below).

Book47
	A	B	C	D
1
2
3
4
5
6
Sheet1