Automatically naming a table as it is created

[Scottie7275]

Good morning,

I have a workbook with a number of worksheets, two of which are named "New Product or Supplier" and "Data".

I currently have some code that takes cells C4 and D4 from the "New Product or Supplier" sheet and creates a table in the next empty column in the sheet "Data".

I would like that table to be named automatically as it is created and the name should be the same as the data in cell C4 in "New Product or Supplier".

I have tried a few different things but I can't get this to work.

Any help is much appreciated.

Thanks
Scott

 

[Akuini]

Could you post the code you are using to create the table?

 

[Scottie7275]

Yes sorry should have posted that in my original.

With Sheets("Data")
        x = .Cells(1, .Columns.Count).End(xlToLeft).Column
        On Error Resume Next
        Set rng = .Cells(1, 1).Resize(, x).Find(what:=Sheets("New Product or Supplier").Cells(4, 3).Value, LookIn:=xlValues, lookat:=xlWhole)
        On Error GoTo 0
        
        If Not rng Is Nothing Then
            .Cells(.Rows.Count, rng.Column).End(xlUp).Offset(1).Value = Sheets("New Product or Supplier").Cells(4, 4).Value
            Set rng = Nothing
        Else
             Dim NextCol As Long
    If Sheets("Data").Range("A1").Value = "" Then
        NextCol = 1
    Else
        NextCol = Sheets("Data").Cells(1, Columns.Count).End(xlToLeft).Column + 1
    End If
    Sheets("New Product or Supplier").Range("C4").Copy Sheets("Data").Cells(1, NextCol)

 

[Akuini]

Try adding this code after the last line of your code above.

---

Please use Code Tags when posting a code. Like this: [CODE ]Your Code Here[/ CODE]

[B][COLOR=Royalblue]With[/COLOR][/B] Sheets[B]([/B][B][COLOR=brown]"Data"[/COLOR][/B][B])[/B]
[B].[/B]ListObjects.Add[B]([/B]xlSrcRange[B],[/B] [B].[/B]Range[B]([/B][B][COLOR=brown]"A1"[/COLOR][/B][B]).[/B]CurrentRegion[B],[/B] [B],[/B] xlYes[B]).[/B]name [B]=[/B] [B].[/B]Cells[B]([/B][B][B][COLOR=crimson]1[/COLOR][/B][/B][B],[/B] NextCol[B]).[/B]Value
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]With[/COLOR][/B]

---

 

[Scottie7275]

Hi,

Thanks for the help.

This throws up an error that a table cannot overlap another table?

Scott

 

[Scottie7275]

Just realised that naming the range that the table covers would also work for what I need. I am simply using the data in the table or range for data validation elsewhere in the workbook.

Thanks

 

[Akuini]

Just realised that naming the range that the table covers would also work for what I need. I am simply using the data in the table or range for data validation elsewhere in the workbook.

Thanks

OK, glad you figured it out.

 

[Scottie7275]

I thought I had but that's not working either!

I can't get it to name the range I just created. It is naming something else instead!

Any ideas?

 

[Akuini]

I don’t quite understand what you're trying to do.
You copy a range from Sheets("New Product or Supplier") to right after the last column in Sheets("Data"). Is the range in Sheets("Data") already a table? If yes that what throws up “an error that a table cannot overlap another table”.
But if the Sheets("Data") already has a table & you copy a range right next to it then the copied range will become part of the table, means you don’t need to create a table.

 

[Scottie7275]

I don’t quite understand what you're trying to do.
You copy a range from Sheets("New Product or Supplier") to right after the last column in Sheets("Data"). Is the range in Sheets("Data") already a table? If yes that what throws up “an error that a table cannot overlap another table”.
But if the Sheets("Data") already has a table & you copy a range right next to it then the copied range will become part of the table, means you don’t need to create a table.

Sheets("Data") is already set up so that each column is a separate table (one column per supplier) and the range is copied right next to it and it does become a table.

I think I might have explained this badly.

As well as being a table, each column is a named range (i.e. the supplier name) so that I can use the INDIRECT function in data validation in another sheet to limit choices to a specific supplier.

What I am trying to do is name the range of the new column automatically so that it can be used in the data validation.

I hope this make sense!

Scott