Copy , Sort & Compute multiple columns using excel macro

[j1nx007]

Dear Members - I am new to excel Macros and require a small help for creating a macro for the below requirement -

1) I have set of raw data in sheet1 (refer attached sheet) .
2) Using a macro i need to

a) copy some columns from Sheet 1 to Sheet 2 - Ship number , shipment , month , Urgency & days (keeping sheet1 formatting)
b) In sheet 2 compute a new column (estimated delivery date) which will be sum of today's date + days
c) Order sheet 2 by Month & urgency desc
d) macro should stop when it reaches an empty line in Sheet 1

Hope experts in this forum can help me out here .

Many Thanks

[PCI]

Sheet2 is always a new sheet or do you want to paste data at the bottom just after "old" info ??

[j1nx007]

Hi PCI -

Sheet 2 is an existing sheet , when the macro is run it should delete old content in sheet 2 and replace it with new values ..is this possible?

[PCI]

Title more explicit now
See next code

Please Login or Register  to view this content.

[Fluff13]

Another option

Please Login or Register  to view this content.

[j1nx007]

Hi PCI & Fluff13 - many thanks for your reply , they have helped me a lot in progressing my requirements and also helped me improve my knowdlege on macros - being sunday today i have not be able to apply the macros to the original spreadee...but it does work on the test data .

Fluff 13 - the solution that you have provided , is it possible to incorporate the below change -

1) Instead of copying the entire sheet 1 to sheet 2 and then deleting the columns that are not required - is it possile to just select and paste only the columns that are required to sheet 2? reason being the orginal spreadsheet had around 50+ columns in it and it will become messy to copy and delete so many coloumns that are not required in sheet2. If this is not possible or difficult then the existing delete solution does work fine .

2) Is it possible to apply a filter and copy only the data that have Shipment = Japan , Mongolia , US , Russia to sheet 2?
3) In sheet 2 is it possible to paste the data from row 3 onwards - leaving the 1st two rows of sheet 2 empty as i wanted to place a text box with the link to run the macro in sheet 2?

Many thanks again ... the solutions that you have supplied have already helped me a lot . thanks again .

[jindon]

See if you can adjust the code to your actual workbook.

Please Login or Register  to view this content.

[Fluff13]

How about

Please Login or Register  to view this content.

[PCI]

Give a chance to next code.
Thx to Fulff13 and Jindon, I reused some of their statements: No shame to copy when it is good ... !
Note: In column "C" do you want to sort by month number or month name ??

Please Login or Register  to view this content.

[j1nx007]

Hi PCI , Jindon & Fluff13 - many thanks for your suggestion on this post . each of your suggesstions have helped me in improving my understanding of macros and fulfill the requirements for my reporting problem .

PCI - wanted to order sheet2 based on month name .

[Fluff13]

Glad we could help & thanks for the feedback