Forum Discussion
VBA Copying data from certain columns one sheet to rows in another sheet
SamFares As mathetes suggested, there is no need for VBA here. In the attached file, I have added an INDEX/MATCH formula to find the Joist Depth in the data table (named it JoistData). But, I needed to clean-up both sheets a little bit.
As said, I gave the original "Table134" (sheet Jedi ....) a more descriptive name, JoistData, and added a column to the far right extracting the first two characters of the Description column, so that I could match them with the joist depths in "Span" column B. To make this work, I got rid of the merged cells in column B (they cause nothing but trouble) and copied down the depths for each group of four rows. Then, I made sure that the row headers used in column D (Span) are exactly matching those used in JoistData (Jedi).
The resulting formula in E5 looks like this:
=INDEX(JoistData,MATCH($B5,'Jedi WN Joists'!$AG:$AG,0)+COLUMN()-COLUMN($E$1)-1,MATCH($D5,JoistData[#Headers],0)).... and it was copied down and across.
Now, there is one weakness in your data. You have two groups, both with a depth of 30. If this is indeed correct, the values for the 2nd group will (or may) be incorrect, using the above formula. I'm referring to items J21 through J40 in the JoistData.
Since you already started noting down the applicable ranges to be copied (column O, Span), you perhaps want to continue on that path. Change texts like "AD2-AD11" to "AD2:AD11" to make it look like a reference to a range, so that you can call it with INDIRECT. The following formula transposes the values for each of the 10 rows in a group (Jedi) to a single row with 10 columns. Its demonstrated in rows 56:59 on the Span-sheet.
=TRANSPOSE(INDIRECT("'Jedi WN Joists'!"&O56))Enter the formula with Ctrl-Shift-Enter if your Excel version doesn't not support dynamic array functions.
Hi, Sam. I notice you've had (as of this writing) 30 views of your message but no replies. I'm going to frustrate you a bit, by also not giving you the macro that you requested.
Part of that is that I generally don't believe in macros; they're OK at times and I use them on rare occasions. But I especially don't believe in them when they're basically just doing by brute force what can be done by elegant Excel functions. And that's my suspicion here; it looks like there must be some relationship between those two sheets that connects the data in the columns with the specific rows you've indicated as targets for the data. I am too unfamiliar with the data, the whole topic (except that we had a contractor in our home yesterday talking of the kind of beam he'd have to put in, recessed, to support the ceiling after we removed the load-bearing wall between our kitchen and dining room).
So I may be totally off base, but this really does look as if a judicious use of functions like MATCH and INDEX or XLOOKUP or FILTER -- each of which is a way to let Excel do the heavy lifting of going and retrieving data according to criteria you set. The criteria in question here in your situation may be embedded or implicit in the column headings, or a combination of some attribute elsewhere in the same row PLUS a column heading.
The point being there's a reason (or a set of reasons) why the data in cells E5 through N5 of your target sheet correspond to the data in column AD2 through AD11 of the source sheet. If you could articulate the reason(s), then I'm sure somebody here, one of us more familiar with the functions I've mentioned above--or some of the others in that same category--could help you see how Excel could do this more elegantly and accurately based on data and integrity of data. The brute force--even if automated via macro--is more error prone because it's just blindly following orders, "Pick this up from here and put it there; don't think, just do."
mathetes Thank you for taking the time to explain.
you asked how "the data in cells E5 through N5 of your target sheet correspond to the data in column AD2 through AD11 of the source sheet." . the data in "Jedi WN Joists" are generated by another software. In column "D" with heading "Description" they describe the steel joist designation. For example, 26LH100/60, indicates the joist depth=26", 28LH300/60 indicates a joist depth =28", and so on. So if you look at column "B" of " Span=50...", it has the joist depth. So what are in column AD2:AD11 for joist depth=26 need to be copied and pasted into E5:N5 which has a joist depth=26. There 4 pieces of data(Wt, w240,Ix, Top Chord Width) that need to be copied and pasted for each joist depth. Thanks!