copying name of a sheet to a drop down list in a cell on another sheet.

[ShawnSPS]

I have written the following code to unhide an hidden worksheet called “Zblank” which is then copied and rename to “a clients name or business” as a new worksheet. What needs to happen is as the user is creating these new client tabs ”worksheets” I need to take the created “new client” and create a dropdown name list of those clients and place them into a worksheet call Znotes on a range of cells. Now I can currently do it manually with data validation but I don’t want to have the user manually updating the list each time they create a new client”spreadsheet” Any Ideas? This is the working code for the creation of those clients.





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 = newName

Sort_Active_Book

Worksheets("zblank").Visible = flase

Worksheets(newName).Activate

[a2].Value = ActiveSheet.Name

Worksheets("zblank").Visible = flase

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:

 

[ShawnSPS]

OK let me redefine what i am trying to do . I created a data validation list that data is (in a hidden sheet) that is used for a drop down in a separate sheet that the user can select from. what I am attempting is to copy what ever business name is created from the above code which is placed in cell A2, what I need it to copy A2 and place in that data validation list that is hidden.called Zdata its range is A1 to A500 is this possible?

 

[smozgur]

@ShawnSPS: Please do not mark a post that doesn't contain a solution. It only reduces the chance of a helper seeing your question and answering it.

 

[ShawnSPS]

well still no answer or even a peep from any one, so thank you but I am going to another forum since I am not getting any answers or inquiries. waste of time.

 

[smozgur]

Sure. Please let them know about this thread and us about that post by providing a link, in case one of them receives an answer to avoid others wasting their time on them separately by providing the same solutions.

This happens from time to time in forums, helpers can't find an easy way to provide a solution. It is mainly when the question is unclear enough to proceed, like in this one. In the original question, there is no indication about the ZData worksheet, but ZNotes. And the question doesn't indicate how the dropdown list is created. It mentions that it could be done by using data validation manually. However, it is not clear if the desired automated method should do that by using a form control drop-down or still data validation (not until post #2 at least). There are other things too, but these are fundamental issues that a helper will go away from due to so many questions to ask because of the lack of information in the original question. It would be basically, a waste of time.

Also, the second post to provide more info makes this question look like having at least one reply, and most helpers would think that it is already answered. That's why it is important to ask the original question by providing information as much as possible.

Regarding the question by assuming some answers to all questions in my mind: The following will add the new worksheet (customer) name at the end of the list on A1:A500:

Worksheets("Zdata").Cells(Worksheets("Zdata").Rows.Count,1).end(xlup).offset(1).value = Activesheet.Name ' or newName - it is also not clear what the current code prefers to use

To make this work dynamically, the data validation should be created as a dynamic source. It is also unknown if the data validation is a dynamic list even after post #2.
It could be done using the List source as follows in this case:

=OFFSET($A$1,,,COUNTA($A$1:$A$500),1)

 

[ShawnSPS]

Well, Honestly if people don't ask questions to clear up any confusing detail their never going know how to help with a solution. it's called collaboration! Since I was not getting any support with the original post I went a different way with it which there is an open post for that, I apologize for being frustrated with the response that I got but your helpers need to ask questions too.

 

[smozgur]

@ShawnSPS - Removed the solution mark since the marked post was not an answer but your final post.

I previously explained how to mark a post as a solution in another thread of yours and post #3 in this thread. That would be great if you could read the instructions about Mark as Solution.

Basically:
Marking a solution doesn't mean closing the thread and there is no such feature on the MrExcel Message Board.
Therefore, please do not mark a post as solution that does not contain an answer.
If your question is not answered or none of the suggested methods solved your question, simply do not mark any post.
If you received an answer that solved your question, or if you answered your own question, then you can mark that post as the solution.

This will help future readers to find what they are looking for quickly.