Copy columns from sheet 1 to sheet 2 using column heading as criteria using VBA

Originally Posted by Jayant shettigar

Thank you very much , I would like to thank each and everyone personally who contributed to this thread , you guys are all really great and are doing a wonderful service to naïve learners like us ....., Thank you once again

You are welcome for my contribution. Thanks for the feedback.
.........................
. And a thanks from me to all the contributers who are doing a wonderful service to naïve nuts like me...
. these sort of threads I really learn lots from.........
..... more on that shortly in my next post here.....

. Alan

@apo….
Hi,

Originally Posted by apo

…..

oops.. that other quote about Rum must have been from another apo.. I swear.. can't have been me …..

. apo, I have the evidence it was you. I may consider a threat to publish…. Let me see… what could be a good ransom…Hmm…. Maybe some explaining comments on your codes or explanation of what is going on with Application.Index with Array as second and Third Arguments… I mean how / why it actually “works” as it does.

Originally Posted by apo

Thanks.. but the real credit should go to snb ……
For example.. his section on Arrays is probably the most comprehensive I have seen.
…..Check out snb's site for more good stuff. …..

. That is a good reference site, but I do not yet get a clear understanding of how the Index stuff is working. ( maybe I just did not stumble on that bit yet – it is a very good extensive and very comprehensive info source )
. ( Shame I have nothing “on“ snb to get a ransom of an explanation. (- If he can: Most experts I have asked do not get past understanding why Application.WorksheetFunction.Index does not work as Application.Index does.. ) )
………………………………………………………………….
. FWIW: ( And for my late reference if no-one else’s!! ), my explanation of what the Index does without understanding “how” or “why”
.
. If I have an Input Array ( say captured from a Spreadsheet range like wot we have been looking at )..

. I have learnt from apo’s codes that there is a neat way to get an output Array built from a combination of “rows” and “columns” form that Input Array. This involves Using a Code line of the form (Psuedo code)
ArrayOut = Index(ArrayIn, rws, clms)
, where rws and clms are Arrays which contain the indices ( 1 2 6 8 etc. ) of the required “rows” and “columns” respectively..

I now know two methods:
The first method ( up until this Thread the only one I knew ) was:

Method1:
. 1a) rws is a 1 dimensional Array ( or 2 Dimensional Array of one “row” ) containing the require output “row” indices. clms is a 2 dimensional Array of 1 column containing the required output “column” indices
. 1b) The above ( .1a) ) produces my required output Array except that the Array is Transposed , so I need to transpose this array to get my required results.

Method2: ( Learnt from this Thread ).
. 2) rws is a 2 Dimensional Array of 1 “column” containing the indices of the required output “rows”. clms is a 1 dimensional Array ( or 2 dimensional Array of one “row” ) containing the required output “columns”

.. which method you use , is I guess based on the one needing the least steps in total – In apo’s last code the Evaluate bit is probably a good quick way to get the “row” indices when all ( or a continuous as in this case with the extra one tacked on the end ) of the “rows” are required. Hence Method 2) is appropriate and not method 1) as I had used!
.
…So……..

@Jacc

Originally Posted by Jacc

Those times you presented are very similar, that's not at all what I got. I'll look in to it tomorrow.

. My results are probably limited by my crapy old computers, or some other errors in my measurements. So I look forward to your results if you get a chance to look further at those codes, for example using the improved Charles Williams Timer.
. To that end here is another code version. Basically the is code stolen from apo’s last code. I understand it at least enough now to use it ( I am still no further at understanding Marc L’s )
. ( My final code is probably just a bit slower that apo’s as I have added a few steps to help me understand it. – By my test it takes the same amount of time as apo’s, but that may be down to my measurement errors or computer as mentioned )

Code:

'
Sub CaptnApoAlan()
'The .Columns.Count+1 in the array is to reference a blank column 'outside' the real CurrentRegion.. Hence allowing for the blank column in the output.
Dim wsDump As Worksheet, wsOut As Worksheet 'Give Abbreviation Methods, Properties of Object Worksheet through .dot
Set wsDump = ThisWorkbook.Worksheets("Dump"): Set wsOut = ThisWorkbook.Worksheets("Output")
Dim rngIn As Range: Set rngIn = wsDump.Range("A1").CurrentRegion 'Range taken in from Dump sheet
    Dim arrin As Variant: Let arrin = rngIn.Offset(1).Resize(rngIn.Rows.Count - 1, rngIn.Columns.Count + 1).Value 'ArrayIn from Row 2 , column + 1 to allowing for the blank column in the output
    Dim rws() As Variant: Let rws() = Evaluate("row(1:" & UBound(arrin, 1) + 1 & ")") '2 Dimensional 1 Column Array
    Dim clms() As Variant: Let clms() = Array(1, 6, 4, 13, 35, 36, UBound(arrin, 2), 19, 28, 15, 2, 3) '1 Dimensional Array
    Dim ArrOut As Variant: Let ArrOut = Application.Index(arrin, rws(), clms()) 'See Post from #20 here for attempted explanation!  http://www.excelforum.com/excel-programming-vba-macros/1094827-copy-columns-from-sheet-1-to-sheet-2-using-column-heading-as-criteria-using-vba-2.html#post4139879
        wsOut.Cells(2, 1).Resize(UBound(arrin, 1), 12) = ArrOut

End Sub

Hope this post is another good contribution to this Thread.
Alan Elston