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

 

[oldbrewer]

if you have the first 3 sheets named tom, **** and harry, and then sheet4 sheet5 up to sheet 10, do you want to copy the first 3 sheets into sheets 4 to 6 ?

and are any new sheets made or used before you want to repeat the operation ?

 

[Fluff]

How about

Sub CopySheets()

   Dim Cnt As Long
   Dim Sht As Long
   Dim Arr() As Variant
   
   For Cnt = Sheets.Count - 2 To Sheets.Count
      Sheets(Cnt).Copy after:=Sheets(Sheets.Count)
      With ActiveSheet
         .Name = Left(.Name, InStr(.Name, " ")) & Format(WorksheetFunction.EoMonth(Date, Month(Date)), "mm dd yy")
      End With
   Next Cnt

End Sub

 

[DRExcel515]

Fluff.. this takes the last 3 sheets in the file and makes copies. Is there a way that if I select 3 sheets by clicking on all 3 of them and then run the macro that it will make copies of only those 3 sheets? Basically I'd like to be able each month to select the 3 sheets that I need to make copies of by clicking on one sheet, then holding Ctrl and clicking on the other two then hitting the macro to copy those specific selected sheets.

Thank you!

 

[Fluff]

How about

Sub CopySheets()

   Dim Ws As Worksheet
   Dim Sht As Long
   
   For Each Ws In ActiveWindow.SelectedSheets
      Ws.Copy after:=Sheets(Sheets.Count)
      With ActiveSheet
         .Name = Left(.Name, InStr(.Name, " ")) & Format(WorksheetFunction.EoMonth(Date, Month(Date)), "mm dd yy")
      End With
   Next Ws

End Sub

 

[DRExcel515]

That works great, except one small issue.... the date went from 11 30 17 to 12 31 18 instead of realizing it was still 2017.

 

[Fluff]

If you always want the previous month, try

Sub CopySheets()

   Dim Ws As Worksheet
   Dim Sht As Long
   
   For Each Ws In ActiveWindow.SelectedSheets
      Ws.Copy after:=Sheets(Sheets.Count)
      With ActiveSheet
         .Name = Left(.Name, InStr(.Name, " ")) & Format(WorksheetFunction.EoMonth(Date, -1), "mm dd yy")
      End With
   Next Ws

End Sub

 

[DRExcel515]

Sorry I should have been a little clearer.

So I will be running this on the 4th business day of the month for the month that just ended (so on Jan. 4th or so I will open the file and want the code to make 3 new copies and the dates be for the 12 31 17 month end). Then in February I would want the copies to have the date of 1 31 18 and son on. Does that help explain?

 

[DRExcel515]

The second code you provided: Works perfectly except the year says 12 31 18 instead of 12 31 17. So if I'm running it today and the tabs I'm selecting are 11 30 17 I'd want them to be copied and say 12 31 17, then the next time be 1 30 18 and so on. Does that help explain the last small issue? Other than the year it works wonderful! Thank you
Sub CopySheets()

Dim Ws As Worksheet
Dim Sht As Long

For Each Ws In ActiveWindow.SelectedSheets
Ws.Copy after:=Sheets(Sheets.Count)
With ActiveSheet
.Name = Left(.Name, InStr(.Name, " ")) & Format(WorksheetFunction.EoMonth(Date, Month(Date)), "mm dd yy")
End With
Next Ws


End Sub

 

[Fluff]

That's what the code in post#7 should do.
Are you saying it desn't work? If so what is it doing wrong?