copy a cell "A2" to a data validation list on another sheet

[ShawnSPS]

Public Sub CopySheetAndRenamePredefined()

On Error GoTo Copysheet_ERR

Dim newName As String

newName = InputBox("Enter New Client Name:")

If StrPtr(newName) = 0 Then

MsgBox ("User canceled!")

GoTo bailout

ElseIf newName = vbNullString Then

MsgBox ("User didn't enter anything!")

GoTo bailout

Else

' MsgBox ("User entered " & newName)

End If

Worksheets("zblank").Visible = True

Worksheets("zblank").Activate

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

ActiveSheet.Name = UCase(Trim(newName))

Sort_Active_Book

Worksheets("zblank").Visible = False

Worksheets(newName).Activate

[a2].Value = UCase(Trim(ActiveSheet.Name))

Worksheets("zblank").Visible = False

bailout:

GoTo Copysheet_EXIT

Copysheet_ERR:

If Err.Number = 1004 Then

Application.DisplayAlerts = False

MsgBox "DEuplicate Client Entered. Press 'OK' then re-run Macro."

Worksheets("Zblank (2)").Activate

Worksheets("Zblank (2)").DELETE

Worksheets("zblank").Visible = False

Application.DisplayAlerts = True

GoTo Copysheet_EXIT

End If

MsgBox Err.Number

Copysheet_EXIT:

End Sub



The above code creates copy of a spreadsheet I made and renames it to what ever the user enters I.E. “ Client Name” Such as SHAWNCO and places the name to A2. The user can make multiple sheets with multiple client names. I need when the user create that sheet that that A2 is copied and is placed in a hidden sheet called “Zdata” Zdata is a data validation list used to create a drop down list in another sheet called Znotes. I know I can take created worksheet SHAWNCO copy A2 from it and place it into zdata that code is



Worksheets(class=tokenstring>"shawnco").Rangeclass=tokenpunctuation>("A2"class=tokenpunctuation>).Copy Worksheetsclass=tokenpunctuation>("Zdata"class=tokenpunctuation>).Range(class=tokenstring>"A1)




Remember the business client name is entered by the user which then that name is placed in A2 on the sheet that it creates.( would be easier to understand if I could send you my excel spreadsheet”



What I need is when each time the user adds(Creates the client) Sheet. That it takes its cell A2 and copies it to zdata which I need it to have it copied sequentially such as A1, a2, a3, a4, a5 and so on. Because that is a data validation list that tied to another sheet called Znotes. Again the above code may help you with understand what its creating.



Thanks

 

[ShawnSPS]

Sorry. Please use this version instead of the previous one I posted. It includes some error checking.

Sub check_sheet_delete()
    Application.ScreenUpdating = False
    Dim mySheet As String, fnd As Range
    mySheet = InputBox("ENTER CLIENT NAME TO DELETE")
    If mySheet = "" Then Exit Sub
    If Evaluate("isref('" & mySheet & "'!A1)") Then
        Application.DisplayAlerts = False
        Sheets(mySheet).Delete
        Application.DisplayAlerts = True
        With Sheets("Zdata")
            .Unprotect Password:="Pila1DA.#"
            Set fnd = .Range("A:A").Find(mySheet, LookIn:=xlValues, lookat:=xlWhole)
            fnd.Delete shift:=xlUp
            .Protect Password:="Pila1DA.#"
        End With
    Else
        MsgBox ("Sheet " & mySheet & " does not exist.")
    End If
    Application.ScreenUpdating = False
End Sub

WORKs Great !! thank you again for the help

 

[mumps]

My pleasure.

 

[ShawnSPS]

Sorry. Please use this version instead of the previous one I posted. It includes some error checking.

Sub check_sheet_delete()
    Application.ScreenUpdating = False
    Dim mySheet As String, fnd As Range
    mySheet = InputBox("ENTER CLIENT NAME TO DELETE")
    If mySheet = "" Then Exit Sub
    If Evaluate("isref('" & mySheet & "'!A1)") Then
        Application.DisplayAlerts = False
        Sheets(mySheet).Delete
        Application.DisplayAlerts = True
        With Sheets("Zdata")
            .Unprotect Password:="Pila1DA.#"
            Set fnd = .Range("A:A").Find(mySheet, LookIn:=xlValues, lookat:=xlWhole)
            fnd.Delete shift:=xlUp
            .Protect Password:="Pila1DA.#"
        End With
    Else
        MsgBox ("Sheet " & mySheet & " does not exist.")
    End If
    Application.ScreenUpdating = False
End Sub

 

[smozgur]

WORKs Great !! thank you again for the help

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.