Sorting worksheets in a workbook

[davidtaylor598]

Hi all,

I have made a macro that copies worksheets from a selection of workbooks, inputs them in the original Excel file, and then sorts them in a specified order using the code below:

Sheets("Valuation Summary").Move After:=Sheets(1)
Sheets("Assets And Liabilities").Move After:=Sheets(2)
Sheets("Foreign Currency").Move After:=Sheets(3)
Sheets("B1").Move After:=Sheets(4)
Sheets("B2").Move After:=Sheets(5)
Sheets("C2").Move After:=Sheets(6)

etc.
This is quite limiting as if just one of the worksheets defined above does not exist then the macro falls down.

I would like to include an IF statement (I think) so that if one of the tabs in the code above is not in the workbook then it will carry on with the rest of the macro and then come up with a message at the end saying something like 'Worksheet XX not copied over as it does not exist in your selection'...

I am also looking to include something that will let me sort the sheets even if the name of the sheet is slightly off (ie maybe spelled incorrectly). Say I have the code to sort the sheet called 'Foreign Currency', but the sheet that has been imported actually has the name 'Foreign_Currency', how would I adapt my code - using the LIKE function? Another example - my code states 'Securities Purchased Payable' but the sheet that has been imported has the name 'Sec_Purch_Pay'...


Any other ways you think I could improve this macro?

If you would like to see the whole code let me know and i'll put it up.

 

[VoG]

Take a look here http://www.vbaexpress.com/kb/getarticle.php?kb_id=72

 

[VoG]

Or ASAP http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=32&utilities=20&lang=en_us