Create Range Name loop

[Svmaxcel]

I had a list with Emp name and manager
i wanted to create Name range, for combo box usage, but there were many common manager names.
So i thought of using pivot tableto create name rang using VBA
please check the attached file
After getting discharged from hospital, my memory has become very dull, so i am hardly able to remember much

I want to create a loop that will create Name Range from pivot table and Stop when the pivot table will end

Sub Macro1()
    Range("F3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.CreateNames Top:=True
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.CreateNames Top:=True

End Sub

 

[Hui]

Try this code:

Sub Setup_Names()
   
    Range("F3").Select
   
    Do Until Selection.Row = 1048576
      Range(Selection, Selection.End(xlDown)).Select
      Selection.CreateNames Top:=True
      Selection.End(xlDown).Select
      Selection.End(xlDown).Select
    Loop
End Sub

 

[Svmaxcel]

Thanks for your reply
It worked well.

I am using 2 Combo box, based on 1st box, Combo box 2nd will filter data.
I have used case select for this.

Sub Combobox1.change
Case "Fruits"
Me.Combobox2 = "Mango"
And so on....
End sub

This is working fine
Now I want to know, if there is any new category, how can I use Case select.

 

[Hui]

Is this related to the previous question ?

If not please start a new thread and attach a sample file

 

[Svmaxcel]

Example attached
so if a new Manager name is added in the table and pivot table is refreshed, new Name range for th manager will be created automatically, changes will reflect in Combbox1, but in case of Combobox 2, there is no Case Select...

 

[Hui]

This question isn't related to the first question and so should have been posted in a new Thread

This is so that there is no confusion when looking for posts

Now try the following code instead of yours

Private Sub Combobox1_Change()
Me.ComboBox2 = ""
Me.ComboBox2.RowSource = Range(Me.ComboBox1).Address

End Sub

 

[Svmaxcel]

I removed all case select and added the aboce code, but nothing worked out

 

[Hui]

Works perfectly for me

I replaced the enter Sub Combobox1_Change() not just the code

Next time, post your file and post details of what isn't working and what should it should be doing

 

[Svmaxcel]

Works perfectly for me

I replaced the enter Sub Combobox1_Change() not just the code

Next time, post your file and post details of what isn't working and what should it should be doing

This worked like a charm, howwver there is small issue here
the example which i posted only had firstname.

So i just added last name to some names, Refreshed Pivot and ran VBA for Comobox, but didnt workout

Rebriefing some more issues
Issue # 1 Post no 2 VBA gives an error as it tried to go beyond last Row of excel(A1048576), it creates name ranges, but gives an error.

Issue # 2 It creates Name Ranges, but overlaps the old Named ranges.
so it keeps the old name as well as new names (i guess it would be better to delete old name ranges in column A)

Issue # 3 While we are using full name with a space in between, excel adds an Underscore to replace the space, due to which they are not recognized and gives error while using VBA, I guess we can use INDIRECT, but no clue of how to use it

Attaching updated file