+ Reply to Thread
Results 1 to 5 of 5

Copying non-contiguous cells from one worksheet to another in a different order

  1. #1
    Registered User
    Join Date
    03-30-2016
    Location
    Florida
    MS-Off Ver
    2007
    Posts
    2

    Copying non-contiguous cells from one worksheet to another in a different order

    We do not always control what spreadsheets we have to work with, although I try!
    I need to go through one spreadsheet and find row meeting certain criteria and then select/copy various cells from that sheet and paste them into another workbook. I have done this successfully when the cells I am copying are 'linear' or 'progressive' (not sure what work is appropriate). i.e.

    Range("AI" & x & ", D" & x & ", E" & x & ", F" & x & ", Q" & x & ", R" & x & ", S" & x).Select
    Selection.Copy
    [switch to another open workbook/sheet]
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    But the spreadsheet I am pasting the values into is set up with the columns in a different order. Here is what I was thinking, although it doesn't work.

    Range("I" & x & ", D" & x & ", C" & x & ", E" & x & ", Q" & x & ", A" & x & ", H" & x).Select
    Selection.Copy
    [switch to another open workbook/sheet]
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Then I stumbled across UNION and that would help but still ended up with the data in the original order.

    Set u1 = Range("A" & x)
    Set u2 = Range("C" & x)
    Set u3 = Range("D" & x)
    Set u4 = Range("E" & x)
    Set u5 = Range("H" & x)
    Set u6 = Range("I" & x)
    Set u7 = Range("Q" & x)
    Union(u6, u3, u2, u4, u7, u1, u5).Copy Workbooks("second_workbook.xls").Sheets(bSheet).Range("A12")

    Any suggestions/guidance is appreciated.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Copying non-contiguous cells from one worksheet to another in a different order

    Hello Bigfoot17,

    Welcome to the Forum!

    The word you used to describe 'linear' cells is Contiguous. Big word meaning the cells touch each other. If not then the cells are non-contiguous or disparate.

    You will need to map the cells from one range to the other using VBA.

    What cells are makeup the destination range?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Copying non-contiguous cells from one worksheet to another in a different order

    Quote Originally Posted by Bigfoot17 View Post

    Range("I" & x & ", D" & x & ", C" & x & ", E" & x & ", Q" & x & ", A" & x & ", H" & x).Select
    Selection.Copy
    [switch to another open workbook/sheet]
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Try this method. Change the sheet names to suit. No need to select sheets or cells.

    Please Login or Register  to view this content.
    This is the same method.
    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 03-30-2016 at 12:57 PM. Reason: typo
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Copying non-contiguous cells from one worksheet to another in a different order

    Do the columns have the same column field labels?

    If so and assuming row x has some unique identifier in one of the columns I'd be inclined to use an Advanced Data Filter on the data to copy the relevant row using the unique identifier in the Criteria range.

    First repeat the column labels in a temporary area using say the label 'blank' in the columns that aren't in your original data (making sure that your original data carries a column headed 'blank'. Then use the Data Filter to extract the unique row to the temporary area then copy the second row of the temporary area and paste it to the sheet in question.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Registered User
    Join Date
    03-30-2016
    Location
    Florida
    MS-Off Ver
    2007
    Posts
    2

    Re: Copying non-contiguous cells from one worksheet to another in a different order

    Forgive my delayed response, I should have replied days ago. I went with the second option, although both work (the code seemed a little tighter). Most importantly I learned something through this that I can use elsewhere. Thank you.

+ 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. [SOLVED] Conditional formatting when copying a worksheet; order of conditions changes
    By jazzmatazz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-30-2014, 06:02 AM
  2. Replies: 0
    Last Post: 06-19-2014, 07:11 AM
  3. [SOLVED] copy and paste from contiguous cells to non-contiguous cells using the = function
    By shameus in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-05-2013, 06:48 PM
  4. [SOLVED] How to sum non-contiguous columns applied as a formula on contiguous cells
    By figo12 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-09-2013, 01:07 PM
  5. [SOLVED] Copying and appending non contiguous colums to a new worksheet
    By Kespin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-21-2012, 10:08 AM
  6. Copying adjacent cells in a particular order
    By rohit330 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-21-2010, 10:19 AM
  7. Replies: 4
    Last Post: 11-01-2009, 04:45 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