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]

Ok, I didn’t test it. The code will create a named range for the copied column.
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]Set[/color][/b] rn [B]=[/B] Sheets[B]([/B][b][color=brown]"Data"[/color][/b][B]).[/B]Cells[B]([/B][B][b][color=crimson]1[/color][/b][/B][B],[/B] NextCol[B])[/B]
c [B]=[/B] Columns[B]([/B]NextCol[B]).[/B]Find[B]([/B][b][color=brown]"*"[/color][/b][B],[/B] SearchOrder[B]:=[/B]xlByRows[B],[/B] SearchDirection[B]:=[/B]xlPrevious[B]).[/B]row
ThisWorkbook.Names.Add name[B]:=[/B]rn[B],[/B] RefersTo[B]:=[/B]Range[B]([/B]Cells[B]([/B][B][b][color=crimson]2[/color][/b][/B][B],[/B] NextCol[B]),[/B] Cells[B]([/B]c[B],[/B] NextCol[B]))[/B]

---

 

[Scottie7275]

Hi,

Sorry for the delay but I have been on holiday.

This doesn't seem to be creating a name but equally it is not throwing up any errors?

I am stumped!

Scott

Ok, I didn’t test it. The code will create a named range for the copied column.
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]Set[/COLOR][/B] rn [B]=[/B] Sheets[B]([/B][B][COLOR=brown]"Data"[/COLOR][/B][B]).[/B]Cells[B]([/B][B][B][COLOR=crimson]1[/COLOR][/B][/B][B],[/B] NextCol[B])[/B]
c [B]=[/B] Columns[B]([/B]NextCol[B]).[/B]Find[B]([/B][B][COLOR=brown]"*"[/COLOR][/B][B],[/B] SearchOrder[B]:=[/B]xlByRows[B],[/B] SearchDirection[B]:=[/B]xlPrevious[B]).[/B]row
ThisWorkbook.Names.Add name[B]:=[/B]rn[B],[/B] RefersTo[B]:=[/B]Range[B]([/B]Cells[B]([/B][B][B][COLOR=crimson]2[/COLOR][/B][/B][B],[/B] NextCol[B]),[/B] Cells[B]([/B]c[B],[/B] NextCol[B]))[/B]

---

 

[Scottie7275]

Hi,

A little more information!

So here is the code that I have which takes the data from the sheet "New Product or Supplier" and pastes into the next empty column in the sheet "Data".

' Create table in data sheet    
    Application.ScreenUpdating = False
          
   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("Data").Cells(1, NextCol).Value = Sheets("New Product or Supplier").Range("C4").Value
    Sheets("Data").Cells(2, NextCol).Value = Sheets("New Product or Supplier").Range("D4").Value

Each column in the sheet "Data" is a separate table and when my macro pastes the new data it appends it to the last table on the sheet "Table 125" which is not what I want. I would like it to create a new table "table 126" and so on. I would also like the macro to define a name for the data in the new table which is the same as the name in the first row of the new table.

I really hope someone can help as I am completely at a loss here!

Thanks

Scott