How to Pull Required Data from Multiple Workbooks to Single Workbook

Brass Contributor

Hello Everyone,

I have multiple files (same template) placed on one location. I need to open file available at folder one by one & pull only required data from each file.

 

Template which have data is attached herewith (form.xlsx) & required output file is also attached.

 

I tried to note down flow of process & tried to devlop code as well however not able to execute completely, can someone look at files & suggest easy code for this flow.

 

Thanks!

 

@Hans Vogelaar can you help on this

 

 

5 Replies
Hi,
If I understand well, the input isn't formatted as a grid with columns and rows, but the output is.
I think you should use named ranges in the input files.
Kind regards
Hans
Hi Hans, Can you elaborate with one example for named ranges?
Hi,
Not sure what you expect from an example...
* In the source file:
- right-click on the source cell you want to use, "Define name", and give a name that you can remember - let's say NAME
See: Define and use names in formulas
https://support.microsoft.com/en-us/office/define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-...
- make sure you know the name(s structure) of the source files - let's say SOURCE
- in the target file, fetch the content of the files using the INDIRECT function
something like
filename content
--------- --------
file1.xlsx =INDIRECT(A2!NAME)

More about INDIRECT in
INDIRECT function
https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261

Does that make sense?

Kind regards

Hans
I need to work on multiple files so using Indirect function it will be little bit difficult.
Can Names will be used in macros? I'm looking to have some guidance on macro in this case.

@Sumit_Bhokare 

In your description, two different cells have to be copied to I2:

 

"Copy from L7:O7 till L56:O56 (or row till we have data) & paste without formatting in cell F2:I2"

"Copy E4 & paste without formatting in cell I2 of required output file"