Sorting in another Sheet

[dhamalian]

Hi,
In sheet 1, I have names starting on B2 and all the way down. Starting on I2, I have values for those names. I am wondering if it is possible to sort these numbers onto sheet 2, largest number on top and decreasing. the values would start on B2 of sheet 2, and the corresponding name, which I'd like to come along with the values, starting on A2 on sheet 2. Is this possible? And can it be so that everytime I put in a new value in sheet 1, it automatically resorts in sheet 2?
thanks so much

 

[tdh777]

Hi there and Welcome !

Simplest solution is to turn the macro recorder on, do exactly what you want to do with your data (ie select the data to be sorted, sort on column I DESCENDING value, then cut and paste the resultant data from column B of sheet 1 to Column A of Sheet 2, and Column I of Sheet 1 to Column B of Sheet 2). Then turn off macro recording and look at the Macro and you will see the VBA code that does exactly what you have just done.

If you then want to use the macro you can either attach it to a button (where you manually press the button after you add a name) , or have it executed every time there is an appropriate change to the Worksheet by linking it to the Worksheet Change event. (You can Google that or look up other examples in this forum using the search term "Worksheet Change event").

That should get you most of the way down an easy-ish track.

Cheers,
Warren,

 

[dhamalian]

It was my first macro and it worked! Thanks!
I have another question.
I have some names on Sheet 1, Column 1, starting at A2, and I want to fill in these same name on a Row starting on B1 and going all the way across Column 1. How do I do this. I am trying to figure out the Offset function but can't get it to work. Any ideas? Thanks!

Hi there and Welcome !

Simplest solution is to turn the macro recorder on, do exactly what you want to do with your data (ie select the data to be sorted, sort on column I DESCENDING value, then cut and paste the resultant data from column B of sheet 1 to Column A of Sheet 2, and Column I of Sheet 1 to Column B of Sheet 2). Then turn off macro recording and look at the Macro and you will see the VBA code that does exactly what you have just done.

If you then want to use the macro you can either attach it to a button (where you manually press the button after you add a name) , or have it executed every time there is an appropriate change to the Worksheet by linking it to the Worksheet Change event. (You can Google that or look up other examples in this forum using the search term "Worksheet Change event").

That should get you most of the way down an easy-ish track.

Cheers,
Warren,

 

[tdh777]

Great ! You've now started into the fun and powerful world of macros !

For your second question, if I have understood you correctly, sounds like you just need to use the paste-special-transpose function. That will take the names from the column (A2 and down) and transpose them into a Row when you paste-special-transpose them into cell B1.

And please mark this question as answered when you're happy so others know it doesn't need answering any more.

Warren

 

[dhamalian]

I ended up using paste special, but if it's possible I would like to have it so that anytime a new name is added in sheet 1, it would automatically add it to sheet 2, instead of using paste special every time.