I'm trying to create a macro that will copy the text from non-blank cells in a select range across multiple sheets and then paste the text into an 8x12 matrix. I've attached a spreadsheet of what I'd like the end result to look like. The cells outlined in sheets 1-6 are the cells I'd like to copy from (if they contain anything), and "Quant" has the matrix end result based on input from the previous sheets. I've input sample data into sheets 1-3. Matrix cells must be filled in order of B12 -> B19, then C12->C19...etc. And, not sure if it matters, but the merged cells in sheets 1-6 must remain merged as well since they are standardized forms.
Reasoning: Clients will fill sheets 1-6 with their information and we build a batch working product that collects (in order) all their entries. The "Quant" sheet is exported into a robotic assay that does not recognize blanks and will treat them as working cells. We can tell it to shut off after a certain number, so blank cells at the trailing end of the matrix are fine.
Thanks for reading!