2

I have a monster of a workbook that I'm trying to make more manageable for those that use it after me. I have a ton of code that is ran when buttons are pressed to make it more user friendly to those that know little to nothing of Excel. So here is where I need help.

I have several sheets with similar tables. My first sheet contains a Master List of customer information and pressing a button, copies this information to each other sheet and sorts it to categorize these customers on their respective sheets. This allows me to enter new information only on the first sheet and have it auto-populate the sheets correctly to minimize human error.

To cut down on a lot of the errors, I utilized structured referencing in tables. I didn't originally have it this way, but I've been trying to improve this workbook over time. Anyway, so I have a column "Charge Type" in each table, and the total column references it as 

    [@[Charge Type]]

which is great, considering customers will be added and removed pretty regularly and this cuts down on errors.

However, when this formula gets copied to one of the other sheets, it's converted over to

    All_List[@[Charge Type]]

which adds the name of the table on sheet1, which is "All_List". Now I want it to refer to the column "Charge Type" specifically in the new table on the new sheet, and I cannot for the life of me figure out how.

EEM
  • 6,601
  • 2
  • 18
  • 33
zcr07
  • 27
  • 2
  • `[Charge Type]` can be used instead of `[@[Charge Type]]` in most cases, but not sure if that would be copied as `All_List[Charge Type]` – Slai Nov 26 '16 at 00:43
  • Suggest to change the title to something like: "Copying ListObjects formulas" – EEM Nov 26 '16 at 15:02

1 Answers1

0

This solution uses a variable to hold the ListObject "Field" formula then loops trough all other ListObjects in the same workbook with the same "Field" and applies the formula to that "Field".

ListObjects before 

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 after enter image description here

EEM
  • 6,601
  • 2
  • 18
  • 33
  • I'll play around with this and see if I can get it to suit my needs. Can I modify this to include multiple columns? As of right now, my code copies an entire table of information(minus the headers), not just a single column. – zcr07 Nov 28 '16 at 17:57
  • This answers the problem mentioned in your post: _"Now I want it to refer to the column "Charge Type" specifically in the new table on the new sheet, and I cannot for the life of me figure out how."_ , suggest to accept the answer and post another question with the details of your problem. – EEM Nov 28 '16 at 18:26
  • Okay, well how about what if it's not the only table in that sheet. Would I use something like ListObjects(2) if it were the second table on that worksheet? – zcr07 Nov 28 '16 at 21:33
  • ListObjects have a name, suggest to use the name, it will be more accurate than using the index. – EEM Nov 28 '16 at 23:24