+ Reply to Thread
Results 1 to 12 of 12

copy non contiguous columns to another sheet in differen order

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    copy non contiguous columns to another sheet in differen order

    Hello everyone
    I am searching for an easy way (using arrays if poosible) to copy non contiguous columns from Source sheet to Destination sheet
    In Source sheet that data has headers (I don't want to depend on the headers) the data starts at row 6
    and in the destination sheet, the data would start at different row which is 10 >> I need the copied columns in different columns ..

    for example:
    Range("A6:A22") in source sheet would be in Destination sheet range("F10:F26") and the same with other columns

    I imagine to have two arrays for column numbers and from these arrays it would be flexible to copy any column in the source sheet to any column in destination sheet

    SourceArray=array(1,2,3,4,5) 'The original column numbers in source sheet
    DestinationArray=Array(6,5,7,4,3) 'The new columns in destination

    Hope it is clear
    Attached Files Attached Files
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: copy non contiguous columns to another sheet in differen order

    Try:-
    Please Login or Register  to view this content.
    Regards Mick

  3. #3
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: copy non contiguous columns to another sheet in differen order

    Does this help?

    Please Login or Register  to view this content.

  4. #4
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: copy non contiguous columns to another sheet in differen order

    Perfect Mr, Mick
    That's really awesome code

    Another and last query related to that code :
    What if I need to copy just columns 1 and 4 in Source sheet and put these ranges to columns 6 and 4 (just these two columns)

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: copy non contiguous columns to another sheet in differen order

    Thanks a lot Mr. John Davis for this useful code .. but in fact I have a lot of columns to deal with so I searched for an easy and effiecient way to do that task
    Thank you very much for sharing the thread

  6. #6
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: copy non contiguous columns to another sheet in differen order

    Perhaps:-
    Please Login or Register  to view this content.
    Regards Mick

  7. #7
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: copy non contiguous columns to another sheet in differen order

    Thank you very much Mr. Mick for this wonderful help

    In fact I still imagine to do the task using arrays ..two arrays one for columns needed to be copied in Source sheet and another arrays for destinations ..


    SourceArray=array(1,2,3,4,5) 'The original column numbers in source sheet
    DestinationArray=Array(6,5,7,4,3) 'The new columns in destination

    Is it possible to store ranges in arrays? and if possible these ranges stored splitted and used in the code (just think loudly!!)

  8. #8
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: copy non contiguous columns to another sheet in differen order

    You can use the same code for an Array "Ray", as below.

    Please Login or Register  to view this content.
    Regards Mick

  9. #9
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: copy non contiguous columns to another sheet in differen order

    Thank you very very much for continuous and wonderful help
    In fact all solutions offered are very awesome ... but I still need more flexible code that deal with columns in both source and destination sheet in flexible way ..
    I noticed in your last code that you deal with the columns in destination using array(5,4,2,1,3)
    That's very well .. I need to deal with columns in source sheet in the same way using array ..as in original file the columns are not adjacent so resize property won't fit my requirement
    Thanks again for all the great help you offered
    Kind Regards

  10. #10
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: copy non contiguous columns to another sheet in differen order

    Perhaps something on the lines of below:-
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: copy non contiguous columns to another sheet in differen order

    Mr. Mick
    I can't express my happiness with this perfect and great illusatration ..You are life savior
    Thank you very much for all this great and wonderful help
    Regards
    Yasser

  12. #12
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: copy non contiguous columns to another sheet in differen order

    You're very welcome

+ 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 contiguous columns and paste as non-contiguous
    By absconditus in forum Excel General
    Replies: 1
    Last Post: 12-29-2014, 03:17 AM
  2. copy and paste columns in a specific order on a different excel sheet.
    By milkfloat in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-08-2013, 01:36 PM
  3. [SOLVED] Need macro to copy columns to a new sheet in a different order
    By dip11 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-07-2012, 10:10 AM
  4. [SOLVED] Separating Data in a Single Cell into differen Columns
    By veejar in forum Excel General
    Replies: 4
    Last Post: 05-26-2012, 01:55 PM
  5. [SOLVED] Copy and paste non-contiguous columns
    By spasmous in forum Excel General
    Replies: 4
    Last Post: 04-12-2006, 04:30 PM
  6. [SOLVED] Create copy of sheet using VB with columns in different order
    By Bryan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-04-2005, 12:06 AM
  7. [SOLVED] How do I reference non-contiguous columns in another sheet and th.
    By Bill Nash in forum Excel General
    Replies: 3
    Last Post: 03-03-2005, 02:06 PM

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