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

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:

' next 2 lines needed for 'Charley Williams Micro Timer Code
Private Declare Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
'
Sub Timers() 'SubRoutine to call Timer Functions and Subroutines under test and display results.
 
Dim StartMTTime As Single, StartVBATime As Single 'times inn seconds at start of a run
Dim MTTime As Single, VBATime As Single 'Run times given from Timer Functions
Let MTTime = 0 'Could leave this out, but good
Let VBATime = 0 'Practice to put it in
Dim Iteration As Long, MaxIteration As Long 'Variable used in avaraging a few runs
Let MaxIteration = 10 'Set here the nimber of runs that you want.
    
    'First Run: timing is often longer first time so do first run without Timers
    Call CaptnApo 'First Run: timing is often longer first time so do first run without Timers
    'Call CopyColumnsAlanSHimpfGlified
    'Call CopyColumnsAlan
    'Call copycolumns_Jacc
    
    'Main Runs to obtain average times
      For Iteration = 1 To MaxIteration 'Run as many times as specified.
      Let StartMTTime = MicroTimer 'Function Code from Charley Williams
      Let StartVBATime = VBATimer 'Typical VBA Timer() Function code
    
    Application.ScreenUpdating = False
      Call CaptnApo
      'Call CopyColumnsAlanSHimpfGlified
      'Call CopyColumnsAlan
      'Call copycolumns_Jacc
    Application.ScreenUpdating = True
      Let MTTime = (MTTime + (MicroTimer - StartMTTime)) 'Total times so
      Let VBATime = (VBATime + (VBATimer - StartVBATime)) 'far.
      Next Iteration 'Go and do another run(s)
    MsgBox "Micro Timer " & (MTTime) / MaxIteration & " Seconds" & vbCr & _
           "VBA Timer " & (VBATime) / MaxIteration & " Seconds" 'Display avarage results.
End Sub ' Timers()

'


Function VBATimer()
'Typical VBA Timer Program
    VBATimer = Timer 'Timer is a VBA Function that gives current time in seconds
End Function ' VBATimer()
Function MicroTimer() As Single 'Charley Williams Micro Timer Code
 
    Dim cyTicks1 As Currency
    Static cyFrequency As Currency
    Let MicroTimer = 0
      If cyFrequency = 0 Then getFrequency cyFrequency ' get ticks/sec
      getTickCount cyTicks1 ' get ticks
      If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency ' calc seconds
    
End Function 'MicroTimer()
'
'
'

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