Macro to Sort and Copy rows

[souvick]

Hi,

I have an excel template (attached) where in Sheet 1 there are 20 rows and 8 columns of data.
In the real excel however there can be 1000's of rows of data and more columns.

I want help in writing two different macros to:

1. Identify Top 10 items on the basis of Schedule Variance percentage and copy the rows to Sheet 2. But I don't want entire row. I want to copy till Schedule variance only

2. Identify Top 10 items on the basis of Cost Variance percentage and copy the rows to Sheet 3. Again I want to omit copying Schedule Variance column from Sheet 1.

Also, before copying the data, the macro should clear out the prevous data entered on Sheet 2 and 3 and leave the heading only. This would help at the time of repeat run and avoid duplicates.

Please find a template attached.

Kindly help

Regards
Paul

[gjcase]

This is easily done using the macro recorder. Just start the recorder, either accept the proposed name or give it a more meaningful one, then step trhu the steps you would take to do it manually. Once finished, stop the recording. Don't worry if you make a misstep during the recording; you can always edit the macro when complete, or re-record it. Doing this is a good way to start learning to write macros. It is instructive to step through recorded macros (use F8 from the VBA editor window with the spreadsheet window displayed so you can see the actions taken.)

I have recorded a macro to your spreadsheet for comparison with what you get. As you get more familar with VBA you will find that the recorder does not necessarily produce the most optimum code, but it generally will get you started.

You should also read up on relative vs absolute addressing & be aware that the recorder is set to absolute by default. Not an issue for this simple macro, but many times it may be.

---GJCase