VBA Creating New WB based on unique Column data and converting new WB ranges to Tables-CodePudding

I posted a question similar to this in the past however a different issue has presented itself.

How existing code works: It creates a new workbook for each unique value, and its duplicates, in column A with all of the associated row data.

The code works perfectly for what I need it to do except when I try to use it on a table range. After some research I realize all of my references are incorrect to be able to do this on a table. I am studying how to do this to resolve my issue long term.

As an short term alternative, I have been looking at how to convert each new workbook range into a table but cannot figure out how to plug this into my existing code. The below sample is where I imagine it should go however I cannot get my head around how to add the command when looking at other examples I have come across in my research.

For Each ky In dic.keys
    ThisWorkbook.Sheets("DEMURRAGE INSTRUCTIONS").Copy
    Set wbN = ActiveWorkbook
    xSht.Range(xTitle).AutoFilter xCName, ky
    Set xNSht = Worksheets.Add(, wbN.Sheets(wbN.Sheets.Count))
    xSht.AutoFilter.Range.EntireRow.Copy xNSht.Range("A1")
    xNSht.Name = xNSht.Range("T2").Value
    ActiveWindow.DisplayGridlines = False
    xNSht.Columns.AutoFit

CodePudding user response：

If you have a contiguous range with headers starting in A1, then this should work to convert that to a ListObject/Table:

With xNSht.ListObjects.Add(xlSrcRange, xNSht.Range("A1").CurrentRegion, , xlYes)
   .Name = "Table1"
End with

CodePudding user response：

Convert Range to Table (ListObject)

This will use the same name for the worksheet and the table.
It will not work if you need to copy entire rows.

For Each ky In dic.keys
    ThisWorkbook.Sheets("DEMURRAGE INSTRUCTIONS").Copy
    Set wbN = ActiveWorkbook
    xSht.Range(xTitle).AutoFilter xCName, ky
    ' You have just copied a single worksheet to wbN hence:
    Set xNSht = Worksheets.Add(After:=wbN.Sheets(1))
    ' Why entire row? No need, unless there is data to the right!?
    With xSht.AutoFilter.Range
        .Copy xNSht.Range("A1")
        With xNSht.ListObjects.Add(xlSrcRange, _
               xNSht.Range("A1").Resize(.Rows.Count, .Columns.Count), , xlYes)
            .Name = xNSht.Range("T2").Value ' or some other table name?
        End With
    End With
    xNSht.Name = xNSht.Range("T2").Value
    ActiveWindow.DisplayGridlines = False
    xNSht.Columns.AutoFit