Copying values from a column and pasting into a row.

**bean29** · 12-14-2012, 08:28 AM

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.

**arlu1201** · 12-14-2012, 08:41 AM

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?

**bean29** · 12-15-2012, 02:49 PM

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

**arlu1201** · 12-16-2012, 10:31 AM

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?

**bean29** · 12-16-2012, 12:39 PM

Yes too both, thanks

**arlu1201** · 12-16-2012, 03:08 PM

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

**bean29** · 12-16-2012, 04:10 PM

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

**bean29** · 12-16-2012, 04:59 PM

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

**arlu1201** · 12-17-2012, 01:46 AM

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.

**bean29** · 12-17-2012, 07:00 AM

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