Create defined name based off another cells value

[gaudrco]

Hi,

I am trying to design code to create a defined name for a cell based off the value of another cell. The cell that contains the name that I would like to be the defined name is in cell (“C2”). The cell that I would like to obtain the defined name is the cell above the new column create from this code:

Private Sub AddCompetitorButton_Click()

With Sheets("Competitor Overview Data").ListObjects("CompOverviewTable")
    .ListColumns.Add(.ListColumns.Count + 1).Name = Range("C2")
    .ListColumns(.ListColumns.Count - 1).Range.EntireColumn.Copy
    .ListColumns(.ListColumns.Count).Range.EntireColumn.PasteSpecial Paste:=xlPasteFormats
End With

The column headers are competitor names, so I want the cell above the column headers to have the defined name of the competitor name, which comes from cell (“C2”).

What do you think?

 

[gaudrco]

I have a command button that creates a new column for a newcompetitor. The button is on sheet ‘Instructions’. On this sheet, a person cantype a new competitor into cell C2 and then click the button. This creates anew column on another sheet with the header of that new column as the value ofC2. I would like to add code to this macro to create a defined name on the cellabove the new column header. The defined names for those cells will be thevalue of C2.

The purpose of this is because I have a dynamic image on adifferent sheet that shows the company logos. The logo changes based off a datavalidation list of values. The dynamic image references the list of valuesbased off the defined names.

Here is a visual explanation

A person types "CompNameThree'in cell C2 of the 'Instructions' tab. Whenthe button is pressed, a new column is add to the CompetitorTable on the"Competitor Overview Data' tab

The cell above the header will have thecompany's logo. This cell needs to have a defined name = C2


CompetitorTable

Defined Name = CompNameOne	Defined Name = CompNameTwo	Defined Name = CompNameThree
CompNameOne	CompNameTwo	CompNameThree
Data	Data	Data
Data	Data	Data
Data	Data	Data
Data	Data	Data
Data	Data	Data

<colgroup><col width="129" style="width: 97pt; mso-width-source: userset; mso-width-alt: 4579;" span="2"> <col width="133" style="width: 100pt; mso-width-source: userset; mso-width-alt: 4721;"> <tbody> </tbody>

 

[Fluff]

Try adding this line just before the End With

   .ListColumns(.ListColumns.Count).Range.Offset(-1).Cells(1, 1).Name = Range("C2")

 

[gaudrco]

This did not work but I felt like it was close. The error I received was "Run-time error '1004': Application-defined error". Thank you for trying. Any other ideas?

 

[Fluff]

What is the range of the table?

 

[gaudrco]

The table range is D4:S52. Thename of this table range is ‘CompOverviewTable’

 

[gaudrco]

I figured it out using a part of your code and part of another source. Here is the code I used

With Sheets("Competitor Overview Data").ListObjects("CompOverviewTable")
    .ListColumns.Add(.ListColumns.Count + 1).Name = Range("C2")
    .ListColumns(.ListColumns.Count - 1).Range.EntireColumn.Copy
    .ListColumns(.ListColumns.Count).Range.EntireColumn.PasteSpecial Paste:=xlPasteFormats
    Names.Add Name:=Range("C2"), RefersTo:=.ListColumns(.ListColumns.Count).Range.Offset(-1).Cells(1, 1)

 

[Fluff]

Glad you sorted it & thanks for the feedback

 

[gaudrco]

Would you have any idea how to changethe scope of the defined name? With this code, the scope is the worksheet but Iwould like the scope to be the Workbook if I could.

 

[Fluff]

When I run that code the range is workbook scope.