VBA Macro for Transposing Columns to Rows on Predetermined Format

Thank you very much. This is exactly what I needed. I appreciate the immediate solution! Thanks again!

Hello,

I appreciate the help I received. The macro worked perfectly. I had another request to the same macro code. I would appreciate the additional assistance if at all possible.

I’m wondering if it’s possible to add a few more column fields to the macro? They can be added wherever it’s easy. I’ve highlighted the additional fields in the attached file. Essentially, I would need to take the additional highlighted fields from the "Original_v2" tab and place them in the associated fields highlighted in tab "Final_v2". I've attached the revised test macro file with the newly added fields (highlighted)below.

Test file_v4.xlsm

Thank you,
Darren

Thanks again. Your comments are really helpful. I will be reviewing the code, but the macro works well.

Hi GC Excel

Originally Posted by GC Excel

Hello …….. the update macro.
I added some comments so you can understand………

. I try to learn from lurking through codes such as yours and have to work very hard to understand them. I just wanted mainly to say how very grateful I am that you put explaining ‘green comments in. It makes a very big difference to how quickly I can benefit from these sorts of codes

. Many Thanks

Alan Elston

P.s. Just a very minor point while I am here. I am simply trying in parallel to the Theory to also benefit from the experience of Profi’s like you….

. With regard to “capturing” as it were the initial data in the first sheet in one go into an Array. I have gone to great length to get it clear in my head exactly how and why to do this. For example here:
…(…
http://www.excelforum.com/excel-new-...to-arrays.html
…. )

. Therefore I would have written this section of your code :

Please Login or Register  to view this content.

…… to something like

Please Login or Register  to view this content.

. I realize the results are identical as shown, for example, in the Watch Window.
. My question is simply.

. 1) As an experienced user you are relying on what I believe are called the “Implicit Defaults” (rather than defining “explicitly”) to give the correct outcome (That is to say an array of values rather than for example a Range Object) as you know that Excel will in this case “guess” correctly.

Or

. 2) In this case is there an extra reason for you doing it the way you have.

. I apologies if it is comes across as very pedantic, I am simply trying to get a feel for actual practice by experienced users in parallel to the “Theory”

. Thanks again

Alan

Hi GC Excel,

Originally Posted by GC Excel

.........
When I pass a range to an array, I usually write :
..............
...... I learned by looking at codes..........
...

..... Many thanks for taking the time to come back for me to a Thread you had already solved very well.
. Sorry if it came across as a bit trivial. I am sort of learning in “isolation” as it were stuck here in the Bavarian Hills. (I am learning excel VBA just to speed up a private but important project..) It makes therefore a big difference to me when trying to learn from codes to have an idea of typical practice or why something may differ from what I may have expected from the “Theory”.
. As does good ‘green comments in Code. Many thanks again for taking the extra trouble to do that. I know from my early days in teaching how hard it can be sometimes when you have to try to explain to someone learning something which you yourself have long since learnt and as such find yourself somewhat obvious.
. Thanks again for going that “extra bit” which sometimes makes a big difference to me “getting it”
Alan.



P.s.
I do note that you choose
ar = ws1.cells(1).CurrentRegion.Value
and not
ar() = ws1.cells(1).CurrentRegion.Value
as I suggested.

. This is an indication perhaps of the experienced way….: –
. After some help from this Forum here
http://www.excelforum.com/excel-new-...to-arrays.html
I finally got the point of mine giving an error for on any occasion that only one cell was referenced. Yours would not produce a 1 dimensional Array in such a case, it would produce one value . But it would not error, which in the practice would probably be preferable, - so again result of experience I guess…..