+ Reply to Thread
Results 1 to 22 of 22

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

  1. #1
    Registered User
    Join Date
    07-30-2010
    Location
    Dubai
    MS-Off Ver
    Excel 2019
    Posts
    46

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

    Hi

    I have two sheets in the attached file , the first sheet Dump and the second sheet is Output
    Data in the dump sheet is in raw format , only certain columns are needed in the Output sheet from the dump sheet

    I have tried writing a code to extract the desired columns from the Dump sheet , to the Output sheet , but the code doesn't seem to work

    Can some one modify the code so that it extracts the way I have pasted the solution in Output sheet , also can the output be made dynamic to extract the column based on the column heading ?


    Thanks
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: 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.
    Last edited by Parth007; 07-21-2015 at 09:39 AM.
    Regards
    Parth

    I appreciate your feedback. Hit * if u Like.
    Rules - http://www.excelforum.com/forum-rule...rum-rules.html

  3. #3
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,607

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

    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.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  4. #4
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

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

    Please Login or Register  to view this content.
    Alan
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  5. #5
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,607

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

    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).
    Last edited by Jacc; 07-21-2015 at 12:20 PM.

  6. #6
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    Quote Originally Posted by Jacc View Post
    ...
    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

  7. #7
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    @ 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

  8. #8
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

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

    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..
    Attached Files Attached Files
    Last edited by apo; 07-21-2015 at 01:16 PM.

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    7,195

    Lightbulb Hi ! Try this demonstration !


    With a trick, Excel basics are fast enough !

    PHP Code: 
    Sub Demo()
        
    With Sheet1.Cells(1).CurrentRegion.Rows
            With 
    .Item("2:" & .Count)
                
    Application.ScreenUpdating False
                
    .Cells(5).Copy Sheet2.[G2]
                .
    AdvancedFilter xlFilterCopy, , Sheet2.[A2:L2]
            
    End With
        End With
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon Add Reputation !

  10. #10
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,607

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

    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.
    Last edited by Jacc; 07-21-2015 at 02:02 PM.

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    7,195

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


    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 !
    Last edited by Marc L; 07-21-2015 at 02:09 PM.

  12. #12
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,607

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

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

  13. #13
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,607

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

    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.

  14. #14
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    Quote Originally Posted by Jacc View Post
    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……..
    Last edited by Doc.AElstein; 07-21-2015 at 06:21 PM.

  15. #15
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    Codes for last speed tests in post #14

    Please Login or Register  to view this content.

  16. #16
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    Quote Originally Posted by apo View Post
    ........ 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
    Quote Originally Posted by apo View Post
    ........ 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?
    ….
    Quote Originally Posted by Doc.AElstein View Post
    ...... 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
    Last edited by Doc.AElstein; 07-21-2015 at 06:08 PM.

  17. #17
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

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

    Thanks.. but the real credit should go to snb for his work and sharing of that work found on his website..

    For example.. his section on Arrays is probably the most comprehensive I have seen.

    http://www.snb-vba.eu/VBA_Arrays_en.html

    “ Yo Ho Ho .. and a Bottle of Rum…….” What - every Day?
    oops.. that other quote about Rum must have been from another apo.. I swear.. can't have been me

  18. #18
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,607

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

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

  19. #19
    Registered User
    Join Date
    07-30-2010
    Location
    Dubai
    MS-Off Ver
    Excel 2019
    Posts
    46

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

    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 nave learners like us

    This has really simplified my work a lot , Thank you once again

  20. #20
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,607

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


    '''''''''

  21. #21
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    Quote Originally Posted by Jayant shettigar View Post
    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 nave 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 nave nuts like me...
    . these sort of threads I really learn lots from.........
    ..... more on that shortly in my next post here.....

    . Alan

  22. #22
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

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

    @apo….
    Hi,

    Quote Originally Posted by apo View Post
    …..

    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.

    Quote Originally Posted by apo View Post
    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
    Quote Originally Posted by Jacc View Post
    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
    Last edited by Doc.AElstein; 08-07-2015 at 07:33 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy Columns to another sheet based on column heading
    By jhall488 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-05-2014, 03:46 PM
  2. [SOLVED] Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words
    By Thomas Andrews in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 06-09-2014, 11:59 AM
  3. how to copy columns of another sheet with the column data of current sheet
    By amethystfeb in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-28-2014, 11:28 PM
  4. [SOLVED] COUNTIF with multiple criteria on several columns (different column heading)
    By rose4emi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-20-2012, 06:41 AM
  5. [SOLVED] Search for heading in data sheet and copy range to corresponding sheet in master workbook
    By sans in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-07-2012, 10:02 AM
  6. Macro to filter based on column heading then copy and paste to new sheet
    By macattackr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2012, 05:14 PM
  7. Search for column heading and copy column onto another sheet
    By chrismann85 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-28-2008, 05:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1