Hi All,
I'm looking to consolidate data from various template files saved within the same folder.
The aim is to cycle through each file and copy a range of data (some are variable in length) and paste them into the master file on a line by line basis one below the other.
I've attached a working copy of the master file tab and 3 tabs representing the 3 individual files, the master file is also linked and colour coded to show what i mean.
Hoping you guys can point me in the right direction.
Last edited by Gti182; 01-04-2017 at 08:41 AM.
try the following macro which will scan your folder and import your data without opening the files.
See the attached workbook with the macro in it.
It might not be great progamming but it works.
Please Login or Register to view this content.
Pierre Leclerc
_______________________________________________________
If you like the help you got,
Click on the STAR "Add reputation" icon at the bottom.
Keeping layout.... row1 lists the cell addresses you want to pull in. So you can change the results of the macro by simply editing the row1 map values. Any cell with a simple address will pull that single cell value. Any cell with an "x" at the end (C19:Cx or D19:Dx) will be treated as a range and multiple rows possibly copied in.
In the macro, edit the fPATH string to point to the folder where you will be importing all the files:
Please Login or Register to view this content.
Last edited by JBeaucaire; 12-23-2016 at 01:35 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thanks for both submissions, away from my computer till the new year but keen to test them as soon as I'm back.
Since it is the Holiday Season, a couple more to try.
The workbook with the code in it needs to be (saved) in the same folder as all the workbooks that have the data in it.
Please Login or Register to view this content.
bPlease Login or Register to view this content.
Thanks for all the submissions. They all work flawlessly as i was hoping and a lot less code than i thought needed.
Interesting to see 3 different approaches taken.
To add to the user experience i'm hoping to add the option for the user to select the folder where the files are stored (changes each month) and adding a hyperlink to each row (perhaps to the client name in column B)
1) macro prompts you to select a folder, it will process all the files within the selected folder.
2) If the addresses listed in row1 include a cell called Filename then the first instance of each file name will include a Hyperlink.
Please Login or Register to view this content.
Thanks all.
Managed to give it a go myself with a combination of above code.
Please Login or Register to view this content.
Last edited by Gti182; 01-04-2017 at 05:10 AM.
improved this bit of code
Please Login or Register to view this content.
double post
Last edited by Gti182; 01-04-2017 at 08:40 AM. Reason: double post
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks