Copy hidden Master sheet rename it and place it at end of workbook

0
votes

I'm trying to copy a hidden page named Master.

When the user clicks a button in a sheet named start it will open an input box. The user will input the new sheet name. The new sheet will be placed after the sheet named start. The user can enter as many sheets as needed with each one being placed at the end. The input box need to have an error check for invalid entries and end sum if the cancel is clicked.

Below is what I have. It just renames the sheet after the start. It is not creating a new sheet with the new name.

Sub Button3_Click()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Sheets("Master")
    Dim newws As Worksheet, sh As Worksheet, newname
    Dim query As Long, xst As Boolean, info As String

retry:
    xst = False
    newname = Application.InputBox("Enter Inmate Name and Number.", info, , , , , , 2)
    If newname = "False" Then Exit Sub
    For Each sh In wb.Sheets
        If sh.Name = newname Then
            xst = True: Exit For
        End If
    Next
    If Len(newname) = 0 Or xst = True Then
        info = "Sheet name is invalid. Please retry."
        GoTo retry
    End If
    ws.Copy after:=ws
    Set newws = ActiveSheet: newws.Name = sh
End Sub
2
excelvba
Have a look at this post: stackoverflow.com/questions/7692274/… Once you have correctly named the new sheet you can move it to whichever place in the Excel file you want. – Ralph
Thank you, This works. I'm new to this, network guy not programing, so this is all new to me. Again thanks I will learn from what you added. – P.Mikita

2 Answers

2
votes

Change the last three.

Application.ScreenUpdating = false
ws.visible = xlSheetVisible
ws.Copy after:=ws
Set newws = ActiveSheet: newws.Name = sh
ws.visible = xlsheethidden
Application.ScreenUpdating = True
End Sub
1
votes

This may do!...

    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Sheets("Master")
    Dim NewName As String: NewName = ""
    Dim sh As Worksheet

Retry:
    NewName = Application.InputBox("Enter Inmate Name and Number.", info, NewName, , , , , 2)
    If NewName = "False" Then Exit Sub 'user shoose 'Cancel'
    For Each sh In wb.Sheets
        If NewName = sh.Name Or NewName = "" Then
            MsgBox "Sheet name is invalid. Please retry."
            GoTo Retry
            End If
        Next sh
    ws.Copy After:=ws
    With wb.Sheets("Master (2)")
        .Visible = True
        .Activate
        .Name = NewName
        End With