Passing Multi-Dimensional Arrays

Hi There,

I'm trying to pass a multidimensional array from one workbook into another in an effort to make a comprehensive summary of project status for all-time. I don't know what's going on but, all of the data makes it into the array, but when I try to move it over to the next workbook it changes types or something and I don't think it's recognized as an Array anymore. Anyone understand why it would be doing this? Or am i just declaring some Variables incorrectly? I have tried battling with this for a while now...


I'm dealing with the following two pieces of code:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I have two workbooks going, the SelectFiles() Is a macro in WB1, and RetrieveData() is a macro found in WB2. I'm trying to have WB1 be a master file that collects data from any files you select in the open dialog box that initially opens up. I want it to open up the files, find where the data is located, pull it out and put it into an array, pass that array back to the master file and dump it into the spreadsheet. Eventually I will be adding more code to add successive values together based on the date in the first column of the array. All variables that are used have been declared within these two files.

The SelectFiles() Macro is in the file 'MasterUpdates.xlsm', when you run this macro, it initally opens up a 'OPEN' Dialog box where the user may select the amount of files to include in the report. This is because we are needing to draw information off several different files (40-50). The user selects the files, and then a new function is called in the line:

Please Login or Register  to view this content.

It goes through the for loop for the files selected by the user, picking one at a time and running RetrieveData() Function.

This function, starts at the top of the sheet and travels down the "Fabrication Tonnage" column until it finds a value other then zero (meaning when fabrication began). Then it starts at the bottom of the page and works it's way up to determine the last day of manufacturing... thus when the project was completed. An array is created based off the size of cells we just found, and data from eleven different columns is extracted. The first column is dates, the rest are all numbers.

It is at this point, I would like to send this array back over to the parent sub where it will dump the information into the sheet Data Dump.

This will be performed for numerous different projects and the goal is to create a master ton/day sheet with a weekly breakout, summing up the total tonnage on each day for every different project. This is where i'm trying to go.

I need to do this in such a way that I can control the first column of the array to be able to sort the data by date, and then combine similar dates by adding everything up. I believe I have made a mistake in the data types i'm calling, or how the function is setup...

I Believe I'm going wrong somewhere in these areas,

in the macro selectfiles()

Please Login or Register  to view this content.

Or in RetrieveData()

Please Login or Register  to view this content.

Does anyone understand where I am going wrong? I have tried so many different things and have been battling with this for a while? I'm stuck...

Thx for all the support on this one..

I just wanted to post the solution in case anyone else ran into this problem. Seems trying to return a value from a different macro on a different workbook, doesn't really work... All I had to do was combine the macro's on the same 'Workbook' and it worked fine... Here is the finished code with a little bit more work done to it.

Please Login or Register  to view this content.