Search code examples
excelstructured-references

How to convert dynamic (structured) reference in Excel to fixed cell value?

I am loading a table into an excel file from a separate file - from this import table I would like to create "sub-tables" defined just by specific columns. Therefore I am using dynamic referencing to see only parts of these tables on a different sheets, what I would like to do in one step is to fix the values from the structured reference at their individual cells. Is there a solution to that, or is it better to use a completely different approach?

Sample table with the table SAMPLE - I would like to fix the values of the references in the columns G,H and J,K,L respectively and convert them into two tabels

Sample table

One of the solution is with the macro which copies and pastes as a number the reference, but it is not very nice and the automation of the procedure is not very smooth. i also tried this solution How do you convert a structured reference in excel to a range? but it does not work for me

Edit: Based on @Ike response I am attaching current VBA code which is copying and pasting specified range to a new worksheet where I can then create a table out of it. But I would like to do that but without the copy-paste process...

    Sub Macro1()
    Range("Sample_range").Copy
    Set NewBook = Worksheets.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks_     :=False, Transpose:=False 
    End Sub
Solution

  • Your code is indeed not very nice.

    Try this one

    Sub convertSpilledRangeToTable(c As Range)

If c.HasSpill Then
    Dim rg As Range
    Set rg = c.cells(1,1).SpillParent.SpillingToRange
    
    rg.Value = rg.Value   'this turns the formula into values
    
    Dim ws As Worksheet: Set ws = rg.Parent
    ws.ListObjects.Add xlSrcRange, rg, , xlYes
End If

End Sub

    You can test it e.g. like this convertSpilledRangeToTable Range("Sample_range") where I assume "Sample_Range" to be G1 from the screenshot.

    Or - if you already create the sub-tables via code - include it there.