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

Hi Jayant,

In Below code you can add the column names accordingly & make it in use... this will work Header wise
This code is only for 5 columns till Debit...

Something like this can be shorten & used

Please Login or Register  to view this content.

The macro recorder will give you the code for the Copy Paste but that is NOT what you should use.
I fixed your code so that it runs, changed names of sheets, not tab name but object name, and a couple of other things but it takes something like 150 seconds to run.

I also wrote a new piece of code looking like below and it takes less then 0.1 seconds to run. This huge time difference is why you should not use Copy Paste in VBA.
I assume this code can be made more compact and faster running by people who are good at VBA but this was the best I could do.

Please Login or Register  to view this content.

Hi Jacc,
. Nice Code.
. ( I think you missed a small bit:

Please Login or Register  to view this content.

Alan

Thanks Doc!
My answer is yes and no. I should have written it as you suggest, I guess this is the proper thing to do.
What I did was to change the programming name of the worksheet manually.
You can do that by selecting a worksheet to the left in the Project Explorer in the VBA editor, then in the properties window below (hit F4 if it's not displayed) you can see the programming name (or object name) at the top and simply change it.

Edit: I see now that the correct term is CodeName, not programming name or object name. You will find it in the Object Browser (F2 in the VBA editor).

Originally Posted by Jacc

...
My answer is yes and no. ....

Ahh , OK. Sorry. - I am a Beginner and I did not know that you could do that . I just tried that, and see that the extra code bit I gave is then not necerssary.
. Useful Info, Thanks

Alan

.P.s. I was attempting a solution as you posted. I will finish it and post it later and include you speed tests for a comparison

@ Jayant shettigar
Hi Jayant shettigar
. I was working on a code for you as Jacc posted his great code..
. As I had almost finished I thought I would still give my alternative.
.
. There are infinite ways to do your requirement, mine is based on a previous solution I gave here, ( where there are some more detailed explanations of what is going on. )
http://www.mrexcel.com/forum/excel-q...l?#post4174322
.
. I give you two codes, basically the same, the first simplified, the second opened up and with explaining Green comments

. I modified my first simplified code to include the Timer part from Jacc as a further comparison. ( And also to run it needs you to the make those changes to the programming names of the worksheet in manually as Jacc explained in Post #5 )
. My codes are almost as fast as Jacc’s
Codes:

Please Login or Register  to view this content.

. Alan

HI..

Here's another one...

Not sure about speed.. it's 'my' Friday night here (and 2.56 am to boot).. had enough Rum to join the league of Pirates i reckon.. ahoy me matees.. in any case..

Try this:

Please Login or Register  to view this content.

The .Columns.Count+1 and then the '37' in the array is so i can reference a blank column 'outside' the real CurrentRegion.. hence allowing for the blank column in the output.

Note: Check out snb's site for more good stuff..

I see the really good guys have arrived
apo, I made a tiny change to your code. I also timed it, something like 10^-8 seconds, fast enough he he...

Please Login or Register  to view this content.

Good looking piece of code there Marc L, very compact. However it takes about 0.6 seconds on my pc, about ten times slower than my code. Also I think you missed the empty column.

The trick is to use a source column and clear it in destination …

But the column is already empty ! Just have to rename its title …

Apo's way is the coolest !

Doc, your code runs at about 0,15s on my pc, pretty good.

Apo's code is very cool and crazy fast! At least one million times faster than the rest of us, can that be right? I'm not sure I trust this timer function for such low numbers.

Originally Posted by Jacc

Apo's code is very cool and crazy fast! At least one million times faster than the rest of us, can that be right? I'm not sure I trust this timer function for such low numbers.

Hi Jacc,
. I am going a bit over both my competence and the capabilities of my 10 year old worn out slow computer here probably:
– I may have over looked something - you could probably do the following better:
. Here are Some speed tests based on an old Thread of mine – see around post # 12 here:
http://www.mrexcel.com/forum/excel-q...looping-2.html
. Basically I am using both the VBA Timer and the Charley Williams Micro Timer, referenced in that Thread above, and pointed out to me by Jerry Sullivan..
.. Here are my initial results for my old Note Book - ( ACER 4810TZG Vista XL 2007. )
. But I should emphasize that in other Threads such as
http://www.mrexcel.com/forum/excel-q...vlookup-9.html
.. I have had very inconsistent results with different computers, XL versions , operating software etc. etc.

Using Excel 2007

	VBA Timer	Charley Williams Micro Timer
Captain apo	0.44	0.44
Alan SHmpfGlified	0.54	0.54
Alan Full	0.55	0.55
Jacc	0.371	0.371

SpeedTests

. I almost understand apo’s code - My code and apo’s are basically similar – stands to reason – These sort of codes I do are usually stolen from versions of his codes!!! Mine would be a bit slower because of unecerssary transposing - I just cannot get the hang of how the Application.Index works exactly with Arrays as it's second and third arguments )
. Yours is new, and a nice alternative. I understand it now
. I will look at Marc L’s later, but I am busting me Nuts just now trying to understand how or if that works - I have no idea yet!!


. Here are the codes I am using for the speed tests:

Please Login or Register  to view this content.

.. I will drop the 4 actual Sub codes I timed in next Post #15 because of the Thread size limitations at Excel Forum……..

Codes for last speed tests in post #14

Please Login or Register  to view this content.

Originally Posted by apo

........ Its (my) Saturday night here.. and 1:41 am also.. few Rums under my belt..so i will keep it short...ish (but reply further when time/less intoxication permits,
..)

and

Originally Posted by apo

........ it's 'my' Friday night here (and 2.56 am to boot).. had enough Rum to join the league of Pirates i reckon.. ahoy me matees.. in any case..
........)

….. ? “ Yo Ho Ho .. and a Bottle of Rum…….” What - every Day?
….

Originally Posted by Doc.AElstein

...... I almost understand apo’s code - My code and apo’s are basically similar – stands to reason – These sort of codes I do are usually stolen from versions of his codes!!! ........……..

…. Hey Guys, while He’s sleeping it off I will sneak around and steal some more of his codes – And while I am there I will see if I can find some documentation to help understand his code quicker….( still a bl--dy mystery to me exactly how the Application.Index works when it has Arrays as its second and third arguments... ( and why Application.WorksheetFunction.Index does not work...) ...)
Alan

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

'''''''''

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:

Please Login or Register  to view this content.

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