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

 

[mumps]

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[ShawnSPS]

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

here is the link to my file thanks for the help : NewCounts.xls

 

[ShawnSPS]

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

Here is what I am looking to happen. When the user presses the add button he/she will enter the Client Name Such as “SHAWNCO” the macro behind it will copy “Zblank” Sheet which is hidden creating a new Sheet called “SHAWNCO” it will name the tab and the client name will be place in Cell A2 on the newly create Sheet.



What I need whenever a new client is created that Client name that populates into A2 needs to be copied and place in the data validation list called “Zdata” which is also hidden. When that name is added to the list that name should now be a part of drop down list in “Znotes” in which the user can select that client when entering notes for that client.



The first half of this al ready works. The tabs you see currently where made before I perfected the name going into A2 on a newly created client Sheet. So when you look at those the client name is not in cell A2 – don’t get confused. Any new client name that is added the client name will populate in A2 – Those other tab sheets where created earlier before that function was created.



These sheets hare protected with a pass word called “Pila1DA.#” so in order to write to them you will need the password which can also before in a couple of the macros just in case the password is lost. If you have any questions you can send me an email through the forum or to ShawnTodero@outlook.com



Thank you

 

[mumps]

Click here to download your file. Please note that I have changed the sheet name Zblank to Ablank so that it always remains as the first sheet. I have also modified the formula for the data validation in the Znotes sheet so that the drop down list updates automatically as a new sheet name is added in column A. This is the macro in Module 3:

Public Sub CopySheetAndRenamePredefined()
    Application.ScreenUpdating = False
    Dim newName As String, i As Long
    newName = InputBox("Enter New Client Name:")
    If newName = "" Then
        MsgBox ("User canceled!")
        Exit Sub
    End If
    If Evaluate("isref('" & newName & "'!A1)") Then
        MsgBox ("Sheet " & newName & " already exists." & Chr(10) & "Please enter a different name.")
        Exit Sub
    End If
    With Sheets("Zdata")
        .Unprotect Password:="Pila1DA.#"
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1) = newName
        .Protect Password:="Pila1DA.#"
    End With
    Sheets("Ablank").Visible = True
    Worksheets("Ablank").Copy After:=Worksheets(Sheets.Count)
    With ActiveSheet
        .Unprotect Password:="Pila1DA.#"
        .Name = UCase(Trim(newName))
        .Range("A2").Value = UCase(Trim(.Name))
        .Protect Password:="Pila1DA.#"
    End With
    For i = 1 To Application.Sheets.Count
      For j = 1 To Application.Sheets.Count - 1
        If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
          Sheets(j).Move After:=Sheets(j + 1)
        End If
      Next
    Next
    Sheets(newName).Activate
    Sheets("Ablank").Visible = False
    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    MsgBox "The tabs have been sorted from A to Z."
End Sub

 

[ShawnSPS]

Click here to download your file. Please note that I have changed the sheet name Zblank to Ablank so that it always remains as the first sheet. I have also modified the formula for the data validation in the Znotes sheet so that the drop down list updates automatically as a new sheet name is added in column A. This is the macro in Module 3:

Public Sub CopySheetAndRenamePredefined()
    Application.ScreenUpdating = False
    Dim newName As String, i As Long
    newName = InputBox("Enter New Client Name:")
    If newName = "" Then
        MsgBox ("User canceled!")
        Exit Sub
    End If
    If Evaluate("isref('" & newName & "'!A1)") Then
        MsgBox ("Sheet " & newName & " already exists." & Chr(10) & "Please enter a different name.")
        Exit Sub
    End If
    With Sheets("Zdata")
        .Unprotect Password:="Pila1DA.#"
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1) = newName
        .Protect Password:="Pila1DA.#"
    End With
    Sheets("Ablank").Visible = True
    Worksheets("Ablank").Copy After:=Worksheets(Sheets.Count)
    With ActiveSheet
        .Unprotect Password:="Pila1DA.#"
        .Name = UCase(Trim(newName))
        .Range("A2").Value = UCase(Trim(.Name))
        .Protect Password:="Pila1DA.#"
    End With
    For i = 1 To Application.Sheets.Count
      For j = 1 To Application.Sheets.Count - 1
        If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
          Sheets(j).Move After:=Sheets(j + 1)
        End If
      Next
    Next
    Sheets(newName).Activate
    Sheets("Ablank").Visible = False
    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    MsgBox "The tabs have been sorted from A to Z."
End Sub

Thank you that worked flawlessly, if you dont mind what was I missing that cause it not work work properly, since I am a novice and still learning vba I like know what I missed or I can improve myself. but that was great I appreciate it

 

[mumps]

You are very welcome.
Here are some suggestions:
Try to avoid using "on error". It is best to design your code to include error checking and have a warning message pop up if an error occurs.
In most cases, you don't have to activate a sheet to perform an action on it. All you have to do is reference the sheet in the code.

 

[ShawnSPS]

You are very welcome.
Here are some suggestions:
Try to avoid using "on error". It is best to design your code to include error checking and have a warning message pop up if an error occurs.
In most cases, you don't have to activate a sheet to perform an action on it. All you have to do is reference the sheet in the code.

I have a related question with Macro. now when the client is created and the name goes to the zdata sheet for the drop down. I have the delete button that deletes the sheet but it the zdata will still hold on to the client name that was deleted ... and would still be in the drop down . here is the macro I had created for the deletion of the sheet, what is the easy way to include when the client is deleted it also remove the name also from the zdata for the drop down .. just to keep it clean

Sub sheet_delete()
Dim ws As Worksheet
Dim mySheet As Variant
mySheet = InputBox("ENTER CLIENT NAME TO DELETE")
Application.DisplayAlerts = False

For Each ws In ThisWorkbook.Worksheets
If UCase(ws.Name) = "" Then
ws.DELETE
End If
Next ws
Application.DisplayAlerts = True
End Sub

 

[mumps]

Try:

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
    End If
    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
    Application.ScreenUpdating = False
End Sub

 

[mumps]

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