I've done something like this "kinda" before but can not remember. I have 200 excel files with data (anywhere from 1,000-15,000 rows of data) in the a column only. I need it all on one worksheet. FORTUNATELY all the files are named p1_1, p1_2, p1_3...p1_200. So I know I can have an indirect function that as I drag over the columns in my master sheet it increases the file name from which it will collect the data on sheet one in column a.
Can you help me remember
SUPER THANKS!!!!!!!!!!!!!!!!!!!!
Hi gannon_w,
It seems to me you can't get data out of closed workbooks. You need to open them, grab the data and copy it into your master and close it again. See if http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.htm helps. If not then there are VBA routines to do this, only if the data in the closed workbooks is pretty uniform.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
I can open all 200 if I can use the indirect function to make it work...Note: I actually have 3 sets of 2-300 files each!!! One by one will take forever!
You really need to attach one of the files so we can see what area needs to be moved to the master sheet. I have some VBA to do this for all files in a folder. Are they all in the same folder and very similar structure? Can you deal with VBA?
Attached...remember There is only data in A column in sheet 1 for each...might be actually 3000 to 10,000 rows though.
They are all in the same folder and very similar structure (all in the same column "A"). I have never used VBA though!
Put this code in a blank workbook in the same directory as all your workbooks. Run the macro and see if it does what you need.
Please Login or Register to view this content.
An error is popping up at this line:
Range(Cells(1, "A"), Cells(lastrow, "A")).Copy
Last edited by gannon_w; 03-09-2012 at 12:36 AM.
Sorry,
It should read
Range(Cells(1, "A"), Cells(LastRowSrc, "A")).Copy
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks