Copy A sheet Near to particular sheet and rename

Question

From this Code I can Copy the sheet and rename but I cant Copy near to desired (particular) sheet. Or I need to search a sheet already in that workbook to select and copy near to it.

Copy sheet > Copy selected.sheet (Active.Sheet) > Copy before sheet > select required sheet (ActiveSheet.Copy Before:) ask for the sheet

Public Sub CopySheetAndRename()
    Dim newName As String

    On Error Resume Next
    newName = InputBox("Enter the name for the copied worksheet")

    If newName <> "" Then
        ActiveSheet.Copy After:=Worksheets(Sheets.Count)
        On Error Resume Next
        ActiveSheet.Name = newName
    End If
End Sub

I Have changed like this but I dont know which portion is wrong or right

Public Sub CopySheetAndRename()
   Dim newName As String 
   On Error Resume Next 
   newName = InputBox("Enter the name for the copied worksheet") 

   If newName <> "" Then 
   On Error Resume Next newName1 = InputBox("Enter the name to copy before worksheet") 

   If newName1 <> "" Then ActiveSheet.Copy before:=Worksheets(Worksheets(newName1).Index) 

   On Error Resume Next 

   ActiveSheet.Name = newName 

   End If 

End Sub 

Edited^^^

I need to Change this code as sheet.name or search sheet

ActiveSheet.Copy After:=Worksheets(Sheets.Count)

I Expect the output to copy a sheet with rename and near to a particular Sheet (if there are 3 sheets, say Sheet1, Sheet2 & Sheet3 If I copy a sheet say sheet1 and copy near to sheet say sheet3 then it must copy before sheet3).

Answer 1

Try this:

ActiveSheet.Copy After:=Worksheets(Worksheets("desired (particular) sheet").Index)

Replace desired (particular) sheet with the name of that special particular sheet you are talking about in your question.

Also, if you want to copy Before just replace the word After by Before

And also, if you want to copy a particular sheet, not the active one, replace ActiveSheet with Worksheets("Nameofthatsheet")

UPDATE #2: OP needs to rename the new worksheet created after copying, so the new code would be something like this:

Public Sub CopySheetAndRename()
Dim NewName As String
Dim SheetToCopy As String
Dim BeforeThisSheet As String
Dim wk As Worksheet
Dim WKexists As Boolean


Get_NewName:

NewName = InputBox("Enter the name for the new worksheet")

If Trim(NewName) = vbNullString Or Len(NewName) = 0 Or NewName = "" Then
    MsgBox "No name has been entered. Copy will be canceled", vbCritical, "ERROR"
    Exit Sub
Else
    For Each wk In ThisWorkbook.Worksheets
        If UCase(wk.Name) = UCase(NewName) Then
            MsgBox "The name entered already exists in this workbook. Please, type a different one", vbCritical, "ERROR"
            GoTo Get_NewName
        End If
    Next wk
End If

SheetToCopy = InputBox("Enter the name for the copied worksheet")

If SheetToCopy = vbNullString Or Len(SheetToCopy) = 0 Or SheetToCopy = "" Then
    Exit Sub
Else
    WKexists = False
    For Each wk In ThisWorkbook.Worksheets
        If UCase(wk.Name) = UCase(SheetToCopy) Then WKexists = True
    Next wk

    If WKexists = False Then
        MsgBox "There is not any worksheet with that name. Copy will be canceled", vbCritical, "ERROR"
        Exit Sub
    End If

End If


BeforeThisSheet = InputBox("Enter the name to copy before worksheet")

If BeforeThisSheet = vbNullString Or Len(BeforeThisSheet) = 0 Or BeforeThisSheet = "" Then
    Exit Sub
Else
    WKexists = False
    For Each wk In ThisWorkbook.Worksheets
        If UCase(wk.Name) = UCase(BeforeThisSheet) Then WKexists = True
    Next wk

    If WKexists = False Then
        MsgBox "There is not any worksheet with that name. Copy will be canceled", vbCritical, "ERROR"
        Exit Sub
    End If
End If

Worksheets(SheetToCopy).Copy before:=Worksheets(Worksheets(BeforeThisSheet).Index)

ActiveSheet.Name = NewName


End Sub

I must admit is not the most elegant way, but it will work.

The code will ask for 3 things:

1. New name for the new worksheet you are going to create
2. Name of the worksheet you want to copy
3. Name of the worksheet where you are going to insert before the copy created with the new name

Also, the code will make sure you use proper worksheet names:

1. In step 1, it will check if the new name already exists or not, because a Workbook cannot have 2 worksheets with same name.
2. In steps 2 and 3, it will check if the names of the worksheets exists, because they need to exists in the workbook. IF you type the name of a non-existing worksheet, process will be canceled.

This is the best I can do. Hope you can adapt this to your needs.