Sorting and copying columns (Forms)

**DanSherwood** · 08-01-2008, 12:14 PM

Hi all, hope you can help me..

I have a table with a defined number of columns, but where the rows will continually be increasing as new data is added.

I want to create some forms where the user can choose which columns from the total list they wish to create a table from and which to sort by - ideally i want them to be able to select this from dropdowns or radio buttons.

Then basically i want a copy of the original table to be produced on a separate tab only using the columns selected and sorted by the columns selected by the user.

So to clarify, if the full table has the columns, age, name, date i want the user to be able to say 'i want a new table creating showing me just the columns age and date, sorted by date' and for this new table to go to a new worksheet with only the columns included they selected, leaving the original table in its original place unchanged.

Any takers?! I'd be eternally grateful! I attach a hypothetical example so someone kind enough might add the functionality and i can then replicate on the proper version..

**Richard Buttrey** · 08-02-2008, 09:01 AM

Hi,

Is it just the columns Age and Date (assuming they are both chosen) that you want sorted, or do you want to allow for any of the chosen columns to be sorted?

How many columns of data are there in your defined table?

Rgds

**Richard Buttrey** · 08-02-2008, 02:02 PM

Dan,

Does something like the attached help, or give you any ideas?

Click the big green button to bring up the menu, then take it from there. The listbox on the left will be populated with however many fields you have on your data sheet. Once you've selected the ones you want, click the Extract button and the listbox on the right will be populated.

Then select a field and choose the appropriate sort option, and finally click the sort button.

HTH

**DanSherwood** · 08-04-2008, 05:04 AM

Hi this is exactly what i needed, thankyou very much indeed.

Is this going to be simple to copy across to another worksheet, I have no VB experience but am assuming a few tweeks to sheet names and ranges in the code once i copy across will be all that is needed?

Thanks a lot,

Dan

**Richard Buttrey** · 08-04-2008, 09:17 AM

Hi Dan,

Yes it should be basically as simple as that. I used range names in the macro so you'll need to make sure you replicate those.

Rgds

**jiaweide** · 10-17-2009, 01:11 PM

Hi Richard,
Your green button macro is great.How can I modify it to copy the selected columns to another workwork? I should be able to give the workbook a name and save it as well (a save msg box). I have another question. Is it possible to have a desktop icon which basicaly has the same function as your green button but it the process is invisible to the user. That is click the icon, input the excel file name, select the columns, type in the new workbook name and location (typical msg box save as).
Thanks