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
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
CodePudding user response:
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.