2

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
Ike
  • 9,580
  • 4
  • 13
  • 29
Jan Tásler
  • 23
  • 4
  • 2
    You definitly need VBA for that - or you have to do it manually: copy the result range (G1: H10) - paste as value and then turn into table. Maybe you should post your VBA - and tell us what is not "very nice" – Ike Nov 15 '22 at 08:51
  • The code I am trying to implement is sort of like this: `Sub Macro1() Range("Sample_range").Copy Set NewBook = Worksheets.Add Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub` This is basically copying the range and pasting to another worksheet - I can workaround with that, but I cannot figure out whether there is a way to just simply create new table without this copy - paste process – Jan Tásler Nov 15 '22 at 10:34

1 Answers1

1

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.

Ike
  • 9,580
  • 4
  • 13
  • 29
  • Oh yeah, this is much better code, but I have slight issue with it - the HasSpill method actually is not True for the range - if I debug it, then it skips the If statement. I cannot figure out why it is not True for the range... – Jan Tásler Nov 15 '22 at 11:45
  • 1
    hard to judge without seeing your data and code. Maybe select `G1` and then run `convertSpilledRangeToTable selection` in the debug window – Ike Nov 15 '22 at 11:51
  • Yes, with specification of the cell it works! Now I can work from that up, thank you! – Jan Tásler Nov 15 '22 at 12:08