Copy selected sheets in same workbook macro

[DRExcel515]

I have a file that every month-end I take the last three tabs in the workbook and make copies of them in the same workbook for the next month-end. Here is the bit of code I have that seems to work to copy one of the selected sheets but just the one. Ideally I'd like to hold down control and click on the last three tabs and hit a command button and have 3 new exact copies of those sheets directly after them. Any help would be much appreciated. Also would it be able to not only copy the three sheets at the end of the file that I've clicked on but also have them re-named? Here is the current naming convention of the 3 tabs: "CRO 10 31 17" "ICO 10 31 17" "SICCRO 10 31 17"

So in November I would have wanted to click on these sheets hit the macro have copies made and had them re-named to "CRO 11 30 17" etc;

Thank you!

Private Sub CommandButton1_Click()


ActiveSheets.Copy before:=Sheets("Review Needed")




End Sub

 

[DRExcel515]

So it is getting hung up on the line> .Name = Left(.Name, InStr(.Name, " ")) & Format(WorksheetFunction.EoMonth(Date, -1), "mm dd yy")

But yest this code would give me exactly what I need! Thank you for all the help I was confusing myself as I was testing it out today when in reality I'd be doing it the 1st week of January and the date would be correct, other than the hang up on the line of code above.

 

[DRExcel515]

Ok I got it to work! Not sure why it was getting hung up but I just re-ran it and it works perfectly! Thank you!

 

[Fluff]

Glad to help & thanks for the feedback