+ Reply to Thread
Results 1 to 10 of 10

Copying values from a column and pasting into a row.

  1. #1
    Registered User
    Join Date
    10-19-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Copying values from a column and pasting into a row.

    Hi,

    I was wondering if someone could help me regarding writing some code that will copy information out of different columns on one worksheet and into a row in a different worksheet.
    I am unsure about how (&where to start) to code this as there are 5 different columns of information and each column can have a different number of values, although the values will be grouped together in lots of 8. Each lot of 8 values are then copied from the column and pasted into one row into cells e.g. A7,B7,C7,D7 etc... The next group of 8 values are then copied and pasted into the next row down e.g A8,B8,C8 etcuntill all lots of data in the column as copied over.

    I have attached an excel sheet with a basic idea of what I am trying to do. I was wondering whether to use a loop but as each column could have any range of data I was unsure if this was the right approach as it may take a long time to loop through?

    Thanks for taking a look at my problem.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Copying values from a column and pasting into a row.

    Will the blue highlighted values always be placed in the right order - like 5 first then 9 then 23 or should the macro search for these numbers and then paste the values?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    10-19-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Copying values from a column and pasting into a row.

    Ideally I would like the macro to search for values in the 'blue' cells i.e. 5, 9, 23 these values may not always be in the correct order. Therefore a search and update would be great.

    Thanks

  4. #4
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Copying values from a column and pasting into a row.

    The format of your All sheet is the same as your original sheet right?

    Also, the sheets for the various numbers - Number1, Number2 will already be created right?

  5. #5
    Registered User
    Join Date
    10-19-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Copying values from a column and pasting into a row.

    Yes too both, thanks

  6. #6
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Copying values from a column and pasting into a row.

    I have worked out the code for you, however it works properly only if the values in column A like 5,9,23 are input by the macro. Would this work for you?

    The code i have so far is -
    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste

    To run the Excel VBA code:
    Choose View | Macros
    Select a macro in the list, and click the Run button

  7. #7
    Registered User
    Join Date
    10-19-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Copying values from a column and pasting into a row.

    Thanks Arlette, I will give it a go and get back to...much appreciated

  8. #8
    Registered User
    Join Date
    10-19-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Copying values from a column and pasting into a row.

    Hi Arlette,
    This macro works great I just had to change Number1 to Numbers1 but other than that it works really well. I really appreciate the work you have put in to helping with this.
    Would it take much more code to search for the values if they were in the wrong order. If you look at column 'Number3' in worksheet 'All', ID 543 and 7, if these were on the worksheet named Numbers 3 and in the order 7 and 543 could theses values be updated with the correct numbers?

    Thanks again

  9. #9
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Copying values from a column and pasting into a row.

    If the numbers in the sheets are in correct order, it can be updated easily through the macro i gave you.

    If they are not, and if its not a problem for you if the data is sorted, then we can sort each data set in the All sheet and then sort the data in the numbers sheets and they will get populated.

    Otherwise, we need to use the Find code but i am somehow not very happy using it. Its my personal opinion and i have always got stuck while using that code. Hence i try other ways. Otherwise if your data cannot be kept in the same order, the find code should help.

  10. #10
    Registered User
    Join Date
    10-19-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Copying values from a column and pasting into a row.

    Thanks for the feedback. As I am creating the worksheets with numbers1, 2 etc I realised that I can link the All sheet to the numbers sheet with formula, therfore the IDs will always be in the correct order I won't require a 'find' code.

    Thank you again for your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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