Pull data from multiple workbooks to populate new workbook

[Mikeymike_W]

Hi,

I want to create a spreadsheet that will pull data from a form that my clients use.

There will be many of these forms and each one will have a slightly different file name but be stored in the same folder (C:\Users\MWa\Desktop\VBA Test\filename)

I want to copy the exact same cells from sheet 3 in the form to the new spreadsheet.

There will be many of these forms within the folder at one time. Can the code read all of these and add a new line in the spreadsheet for each form with one click?

I realise I'm asking a lot but I'm hoping the excel gods are watching over me

Many thanks in advance,

Mike

 

[tomlund]

I found a way where it retrieves all values, however, for the second range it adds vertically instead of horisontally.

So currently it visulizes the data as below.

Sheet nr	Sales	Cost
1	200	100
2	300	150
	Worker ID	Salary
1	10	20
2	11	19

Instead i want it as

Sheet nr	Sales	Cost	Worker ID	Salary
1	200	100	10	20
2	300	150	11	19

 

[Fluff]

To get the values in the same row you need to resize the range like

Rng.Offset(, 10).Resize(, 10).Value

 

[tomlund]

Many many thanks! It worked! :D

 

[Fluff]

You're welcome & thanks for the feedback.