Структурная ссылка изменяется на абсолютную при копировании на листы

Question

У меня есть монстр рабочей книги, который я пытаюсь сделать более управляемым для тех, кто использует ее после меня. У меня есть тонна кода, который запускается при нажатии кнопок, чтобы сделать его более удобным для пользователей, тех, кто практически ничего не знает об Excel. Так вот, где мне нужна помощь.

У меня есть несколько листов с похожими таблицами. Мой первый лист содержит основной список информации о клиентах и, нажав кнопку, копирует эту информацию друг на друга и сортирует ее по категориям этих клиентов на соответствующих листах. Это позволяет мне вводить новую информацию только на первом листе и автоматически заполнять ее, чтобы свести к минимуму человеческие ошибки.

Чтобы сократить количество ошибок, я использовал структурированные ссылки в таблицах. Изначально у меня такого не было, но я пытался улучшить эту книгу с течением времени. Во всяком случае, поэтому у меня есть столбец "Тип оплаты" в каждой таблице, и общий столбец ссылается на него как

    [@[Charge Type]]

это здорово, учитывая, что клиенты будут добавляться и удаляться довольно регулярно, и это сокращает количество ошибок.

Однако, когда эта формула копируется на один из других листов, она преобразуется в

    All_List[@[Charge Type]]

который добавляет имя таблицы на sheet1, которая является "All_List". Теперь я хочу, чтобы он ссылался на столбец "Тип начисления" конкретно в новой таблице на новом листе, и я не могу на всю жизнь сообразить, как.

Answer 1

Это решение использует переменную для хранения ListObject Формула"поля" затем проходит через все остальные ListObjects в той же книге с тем же "полем" и применяет формулу к этому "полю".

ListObjects перед

Sub ListObjects_Formula_Copy()
Dim wsh As Worksheet
Dim lob As ListObject
Dim rTrg As Range
Dim sFld As String
Dim sFmlR1C1 As String

    Rem Get Formula from Primary ListObject
    sFld = "Price"                                                      'Change as required
    Set lob = ThisWorkbook.Sheets("Sht(0)").ListObjects(1)              'Change as required
    sFmlR1C1 = lob.ListColumns(sFld).DataBodyRange.Cells(1).FormulaR1C1

    Rem Apply Formula to Other ListObjects
    For Each wsh In ThisWorkbook.Worksheets
        If wsh.Name <> "Sht(0)" Then
            For Each lob In wsh.ListObjects
                Rem Validate Field
                Set rTrg = Nothing
                On Error Resume Next
                Set rTrg = lob.ListColumns(sFld).DataBodyRange
                On Error GoTo 0
                Rem Applies Formula
                If Not (rTrg Is Nothing) Then rTrg.FormulaR1C1 = sFmlR1C1
    Next: End If: Next

End Sub

ListObjects после