Copy sheet into 3D array

Hello forum,

I need to be able to copy a sheet into a 3D array with the first dimension being values in rows of sheet, the second dimension being values in columns of sheet and the third dimension being set by a variable(sample number). I need the data for previous samples (speaking of this same code ran with the "sample" variable for the third dimension being a different value) to stay intact as more sheets are loaded into the array. I have some halfway working code, but I'm still far from finished. Any help or suggestions are appreciated. (the functions are in a separate module in my real spreadsheet)

Please Login or Register  to view this content.

p.s. I posted this problem to mrexcel forum earlier today at the following link https://www.mrexcel.com/forum/excel-...-3d-array.html I will inform both forums when/if I find a solution.

I haven't heard from anyone on either forum (here at excelforum and at mrexcel) yet. I will inform both forums when either I find a solution or I get help from someone.

For this chunk of the problem I am just trying to copy sheets into an array where dimension one of the array is what sheet(what I called sample in my earlier post) is being stored, dimension two corresponds to what row the data is on and dimension three corresponds to what column the data is on.

The array would look something like the following:

Please Login or Register  to view this content.

Where I could loop through each sheet and copy the data from said sheet into the row(2) and column(3) of the array.

Although it doesn't work, I envisioned something like the following:

Please Login or Register  to view this content.

I hope that was a little more clear, I'm still learning, and I don't know the best way to explain (or do) things yet.

Thank you for your advice so far.

I've never used a dictionary, I will look into it and see about it.

I've been playing with getting a 3D array defined with sheet, row, column most of the day, and I feel like I'm close, but just can't get the syntax correct.
This is what I have so far:

Please Login or Register  to view this content.

So I can keep track of which sample is which while I am performing math on them all, they will all then be copied back to sheets and plotted.

I'm not opposed to your suggestion of a dictionary, I just don't know what they are and how to use them. I will have to read about them a bit.

EDIT 16:21CST: I have a bit of experience with 1D array and 2D array, but this is the first 3D array I've tried and it is more difficult as I cant just copy a row to the array or a sheet to the array due to the extra dimension.

Well, I was able to test it out with some data in a similar format as the end use and it appears to work.

However, the dictionary disappears when the sub is complete, is there a way to make it persevere until manually deleted or cleared?

Thank you for your help thus far, you have been more helpful than everyone else, and to add to that you taught me of a feature that I've never used before...dictionaries.

By another piece of code that a different button runs, but if that is the case then I can load into dictionary, do all calculations, rearrange in correct order, resave to sheets, and then pull data back off from sheets when(if) I need to access it again.

I'm going to try to shift some things around in my flow chart to keep that from being a need, I wont know until I get back to work in the morning.

Thanks again for all of your help!

Yes, I like that idea a lot. I can even pass the number of points to it that way so it isn't hard coded in case I ever get data from someone else that has a different number of points.

Please Login or Register  to view this content.

It will take me the better half of today to get this incorporated in my spreadsheet and rearrange a few things, but I will let you know when I'm done how it turns out....I'm optimistic, this feels like a good solution.

Thank you for all of your help and time, I never would have arrived at this solution without your help.

I was trying to modify it to only take the sheets that end with a particular suffex, but got stuck again. I don't think it is seeing my wildcard correctly.

This is what I currently have, any idea where I am messing up?
Or if there's a different or better way to make it run only on sheets that end with _RAW (or _COR sometimes, or _IDEAL sometimes, or _ZERO sometimes).

Please Login or Register  to view this content.

Okay cool, I think I have exactly what I need now! Thank you all your help and for putting up with me as I learn how to work with VBA.