excelvbaautofilterexcel-tableslistobject

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

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

Original Post found here.

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
Solution

  • 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