macro to organise location of new duplicated sheets automatically

Hi All,

i've created the following macro on excel 2016 below which duplicates template sheets. i have three different kinds, a CAI, CCR, and EWQ template macro. after each sheet is copied the user will enter in a number to name it, so the sheet names will look like, for example, CAI 001, CAI 002, CCR 001, etc.

I've set the macro to insert the new sheet at the end of the workbook every time - but is there a way i can organise the location of the new duplicated sheet to automatically be moved after the section? for example, if i add a new CAI sheet, it doesn't go to the end of the workbook, but instead goes after the last CAI numbered sheet, but before the CCR sheet section starts? and then all the CCR sheets are in the middle, but the EWQ numbered sheets will always go to the end anyway.. hopefully that makes sense!


# Sub Copy_CAI_Template()

Dim Actsheet As String
Application.ScreenUpdating = False
On Error Resume Next
ActiveWorkbook.Sheets("CAI Template").Visible = True
ActiveWorkbook.Sheets("CAI Template").Copy _
After:=Sheets(Sheets.Count)
ActNm = ActiveSheet.Name
ActiveSheet.Name = "CAI " & InputBox("Enter new CAI reference number")
Sheets(ActiveSheet.Name).Visible = True
ActiveWorkbook.Sheets("CAI Template").Visible = False
Application.ScreenUpdating = True
End Sub #


i'd appreciate any help!

Perhab like this

Please Login or Register  to view this content.

Or you can try change this line

Please Login or Register  to view this content.

Become

Please Login or Register  to view this content.

Hi TEexcel

Your post does not comply with rule2 of the Forum's rules...

2. Programming code must be enclosed in code tags to improve readability. (A, Z)

Please Login or Register  to view this content.

(or use the # button)

To expedite a solution, please comply with the above...Also add a sample file to make use of...