Copy sheet based on Inputbox - data to be copied variable.

I have many sheets named in the MMM YYYY format, and I want an Inputbox to ask what sheet they want copied.
What I want the macro to do is take the sheet the user wants, copy it to the last sheet and rename it as MMM YYYY + 1 year. I do not want ALL rows to be copied though, only those that do not have a certain text string in a cell.

Ie if the user types in Aug 2012 to be copied, Aug 2012 sheet is copied to the last sheet and is renamed Aug 2013. but I want only the rows of data to be copied that do NOT have the status of "NTU", "Declined", "Non-Renewed" or "Extended" in Colomn I17:I190. All other data in the sheet is to be copied.

copytest.xls

Wow that works great, the only snag is the

Please Login or Register  to view this content.

is there any way this can be changed so that it clears the contents rather than deletes the entire row?

I need it that way as I have an event macro that sorts the rows and it knackers my row alignment further down the sheet.

ignore me, I figured it out by replacing .delete with .clearcontents!!

thank you so much!

Oh before i go - is there code that can be put in that stops the sub if the user cancels the inputbox?

Absolutely brilliant. I googled that for ages and havent come up with that Cbool code.

thanks again!

Strange it works for me! I will test your new code when I get to my pc tonight

tried both, both work for me.....?!

Jakobshavn
Ah i see now your original code doesnt work if you DONt hit cancel. Thanks for the update.

Sorry to keep bugging you with this, but is there a way to enhance the code to say that if there is no sheet available to copy when the user is asked (for example if the user asks to copy Jul 2012 and no Jul 2012 sheet exists) that there is an error message and the sub is exited.

Furthermore can the user be forced to enter in the format Mmm YYYY ?

Noticed a problem with the

Please Login or Register  to view this content.

Instead of clearing the entire row how do I change so it clears the range A:K instead?

Also my efforts of putting in a messagebox to say a "sheet has been requested that doesnt exist" has failed - how can this be implemented?

Brilliant that does the trick.

Any chance Msgboxes could be used if there is a problem rather than just exiting sub straight away?

hehe what a pain i am.....I am no software designer, just massively ****!!

I am writing this for people who need to have their hands held at every turn when it comes to Excel so want it as clear as possible!

as i mentioned before another issue is the

Please Login or Register  to view this content.

this needs to be changed to only the contents of A:K are cleared. as my formulas in colomns L:O and AH:AI are removed which i dont want.

wonderful it works!

you are gonna hate me when you get to my next issue!!

http://www.excelforum.com/excel-prog...n-formula.html