Create new sheet and create table within

1
votes

I would like to create new worksheets, name the new worksheets, add columns to the worksheets, and then make a table in each new spreadsheet, all in a workbook that has one sheet to begin with. My code doesn't work, but to see where I am with this so far:

Sub Create_Sheets()

Sheets.Add.Name = "VA_NAME"
Sheets.Add.Name = "VA_VALUE"
Sheets.Add.Name = "CE_NAME"
Sheets.Add.Name = "CE_VALUE"

Dim Table As ListObject
Set Table = Sheet1.ListObjects("VA_NAME")

Table.ListColumns.Add 1
Table.HeaderRowRange(1) = "SOURCE_SEQ_NBR"
Table.ListColumns.Add 2
Table.HeaderRowRange(2) = "L1_PARCEL_NBR"
Table.ListColumns.Add 3
Table.HeaderRowRange(3) = "L1_ATTR_TEMP_NAME"
Table.ListColumns.Add 4
Table.HeaderRowRange(4) = "L1_ATTR_NAME"
Table.ListColumns.Add 5
Table.HeaderRowRange(5) = "L1_ATTR_VALUE"

Set Table = Sheet1.ListObjects("VA_VALUE")
Table.ListColumns.Add 1
Table.HeaderRowRange(1) = "SOURCE_SEQ_NBR"
Table.ListColumns.Add 2
Table.HeaderRowRange(2) = "L1_PARCEL_NBR"
Table.ListColumns.Add 3
Table.HeaderRowRange(3) = "L1_ATTR_TEMP_NAME"
Table.ListColumns.Add 4
Table.HeaderRowRange(4) = "L1_ATTR_NAME"
Table.ListColumns.Add 5
Table.HeaderRowRange(5) = "L1_ATTR_VALUE"

Set Table = Sheet1.ListObjects("CE_NAME")
Table.ListColumns.Add 1
Table.HeaderRowRange(1) = "SOURCE_SEQ_NBR"
Table.ListColumns.Add 2
Table.HeaderRowRange(2) = "L1_PARCEL_NBR"
Table.ListColumns.Add 3
Table.HeaderRowRange(3) = "L1_ATTR_TEMP_NAME"
Table.ListColumns.Add 4
Table.HeaderRowRange(4) = "L1_ATTR_NAME"
Table.ListColumns.Add 5
Table.HeaderRowRange(5) = "L1_ATTR_VALUE"

Set Table = Sheet1.ListObjects("CE_VALUE")
Table.ListColumns.Add 1
Table.HeaderRowRange(1) = "SOURCE_SEQ_NBR"
Table.ListColumns.Add 2
Table.HeaderRowRange(2) = "L1_PARCEL_NBR"
Table.ListColumns.Add 3
Table.HeaderRowRange(3) = "L1_ATTR_TEMP_NAME"
Table.ListColumns.Add 4
Table.HeaderRowRange(4) = "L1_ATTR_NAME"
Table.ListColumns.Add 5
Table.HeaderRowRange(5) = "L1_ATTR_VALUE"

Columns.AutoFit

I would prefer that the code recognize the sheet name (i.e, "VA_NAME") rather than by "Sheet2, Sheet3" and so on. I know "Set Table = Sheet1.ListObjects("VA_NAME")" is far more than likely my problem, as this relies on "Sheet(X)" convention, but doing Sheets("VA_NAME").ListObjects doesn't work. Is there a quick fix to this, or is what I'm asking against VBA's rules?

Thanks, all!

UPDATE: It's occurred to me that my posted code assumes that tables already exist, and that is not the case. I have added new code to create actual tables, but without named columns:

Sheet2.ListObjects.Add(xlSrcRange, Range("$A$1"), , xlNo).Name = "VA_NAME"
Sheet3.ListObjects.Add(xlSrcRange, Range("$A$1"), , xlNo).Name = "VA_VALUE"
Sheet4.ListObjects.Add(xlSrcRange, Range("$A$1"), , xlNo).Name = "CE_NAME"
Sheet5.ListObjects.Add(xlSrcRange, Range("$A$1"), , xlNo).Name = "CE_VALUE"

My order of operations should be Create Named Sheets --> Create Named Columns --> Create Tables from Columns.

2
vbaexcel

2 Answers

0
votes

While I doubt this is the most intuitive way to go about creating named sheets with named tables and columns, this is what I did (I am still very open to suggestions):

Sub Create_Sheets()

Sheets.Add.Name = "VA_NAME"
Sheets.Add.Name = "VA_VALUE"
Sheets.Add.Name = "CE_NAME"
Sheets.Add.Name = "CE_VALUE"

End Sub

Sub Create_PARCEL_ATTR_Tables()

Sheets("VA_NAME").Select
Sheet2.ListObjects.Add(xlSrcRange, Range("$A$1:$E$1"), , xlNo).Name = "VA_NAME"
Sheets("VA_VALUE").Select
Sheet3.ListObjects.Add(xlSrcRange, Range("$A$1:$E$1"), , xlNo).Name = "VA_VALUE"
Sheets("CE_NAME").Select
Sheet4.ListObjects.Add(xlSrcRange, Range("$A$1:$E$1"), , xlNo).Name = "CE_NAME"
Sheets("CE_VALUE").Select
Sheet5.ListObjects.Add(xlSrcRange, Range("$A$1:$E$1"), , xlNo).Name = "CE_VALUE"

End Sub

Sub Create_PARCEL_ATTR_COLUMNS()

Sheets("VA_NAME").Range("A1").Value = "SOURCE_SEQ_NBR"
Sheets("VA_NAME").Range("B1").Value = "L1_PARCEL_NBR"
Sheets("VA_NAME").Range("C1").Value = "L1_ATTRIB_TEMP_NAME"
Sheets("VA_NAME").Range("D1").Value = "L1_ATTRIB_NAME"
Sheets("VA_NAME").Range("E1").Value = "L1_ATTRIB_VALUE"
Sheets("VA_NAME").Columns.AutoFit

Sheets("VA_VALUE").Range("A1").Value = "SOURCE_SEQ_NBR"
Sheets("VA_VALUE").Range("B1").Value = "L1_PARCEL_NBR"
Sheets("VA_VALUE").Range("C1").Value = "L1_ATTRIB_TEMP_NAME"
Sheets("VA_VALUE").Range("D1").Value = "L1_ATTRIB_NAME"
Sheets("VA_VALUE").Range("E1").Value = "L1_ATTRIB_VALUE"
Sheets("VA_VALUE").Columns.AutoFit

Sheets("CE_NAME").Range("A1").Value = "SOURCE_SEQ_NBR"
Sheets("CE_NAME").Range("B1").Value = "L1_PARCEL_NBR"
Sheets("CE_NAME").Range("C1").Value = "L1_ATTRIB_TEMP_NAME"
Sheets("CE_NAME").Range("D1").Value = "L1_ATTRIB_NAME"
Sheets("CE_NAME").Range("E1").Value = "L1_ATTRIB_VALUE"
Sheets("CE_NAME").Columns.AutoFit

Sheets("CE_VALUE").Range("A1").Value = "SOURCE_SEQ_NBR"
Sheets("CE_VALUE").Range("B1").Value = "L1_PARCEL_NBR"
Sheets("CE_VALUE").Range("C1").Value = "L1_ATTRIB_TEMP_NAME"
Sheets("CE_VALUE").Range("D1").Value = "L1_ATTRIB_NAME"
Sheets("CE_VALUE").Range("E1").Value = "L1_ATTRIB_VALUE"
Sheets("CE_VALUE").Columns.AutoFit

Thanks, all!

0
votes

If you code above works, this would shorten it and make it less cumbersome.

Sub Create_PARCEL_Stuff()

Sheets.Add.Name = "VA_NAME"
Sheets.Add.Name = "VA_VALUE"
Sheets.Add.Name = "CE_NAME"
Sheets.Add.Name = "CE_VALUE"
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = "NAME OF YOUR SHEET WITH DATA" Then
            'Do Nothing
        Else
            ws.ListObjects.Add(xlSrcRange, Sheets("Name of source sheet").Range("$A$1:$E$1"), , xlNo).Name = ws.Name
            ws.Range("A1").Value = "SOURCE_SEQ_NBR"
            ws.Range("B1").Value = "L1_PARCEL_NBR"
            ws.Range("C1").Value = "L1_ATTRIB_TEMP_NAME"
            ws.Range("D1").Value = "L1_ATTRIB_NAME"
            ws.Range("E1").Value = "L1_ATTRIB_VALUE"
            ws.Columns.AutoFit
        End If
    Next ws
End Sub