Table question - Adjust one table column list, have it automatically update other worksheets

[LlebKcir]

One of my workbooks has a default template table that all of the other worksheets are based on. One goal for this workbook is to keep all worksheets using that template identical. Currently If I add, move, or remove a column on the template worksheet, I then have to manually perform the same task on all other worksheets. Excel does not like copy/paste columns into tables. Complains that you are moving cells around, well duh thats what I want to do silly Excel.

current process:

1. manually insert new column into template & give new column header title.
2. repeat for all worksheets in the workbook.
3. If adding formulas into new column, manually copy/paste after creation of new columns to all worksheets.

This is both time consuming and a bit of an irritant.

desire:

1. manually update template worksheet
2. all other worksheets receive the new column with any code associated with new column

This does not need to be "real time" can be a forced function from a button, F5, etc...

Directions on if this is A possible, B if possible, what path to head down to start my research on how this is accomplished. Not even sure what questions to ask the all mighty googs to start :D.

 

[Yongle]

Please make this amendment
I moved the line from inside to outside the sub and forgot to change to private

'ORIGINAL LINE
Dim c As Long, ws As Worksheet
'SHOULD BE
Private c As Long, ws As Worksheet

 

[LlebKcir]

Thank you, I will work on that today and report back after testing.

 

[LlebKcir]

Sorry for such a long time to report back. Work >>> project to improve performance.

Your code works great, below is the slight adjustment you asked for to make life easier for others that might have a similar situation

Option Explicit

Private Master As ListObject, M As Range, cM As Long
Private Tbl As ListObject, T As Range, cT As Long
Private c As Long, ws As Worksheet

Sub AdjustTables()
'Master Table
    Set Master = Sheets("Template").ListObjects("tTemplate")
    Set M = Master.Range
    cM = M.Columns.Count
'loop sheets
For Each ws In ThisWorkbook.Sheets
    If ws.ListObjects.Count > 0 Then
        Set Tbl = ws.ListObjects(1)
        Set T = Tbl.Range
        cT = T.Columns.Count
       
        Select Case ws.Name
            Case "Template", "Cover Sheet", "Raw Data"             'list sheets to be ignored separated by commas
                'do nothing
            Case Else
                If cM > cT Then InsertColumn Else DeleteColumn
        End Select
    End If
Next ws
End Sub

Private Sub InsertColumn()
    For c = 2 To cM
        If WorksheetFunction.CountIf(Tbl.HeaderRowRange, M(1, c)) = 0 Then
'insert the column and reset the range
            If c > cT Then Tbl.ListColumns.Add Else Tbl.ListColumns.Add Position:=c
            Set T = Tbl.Range
'format the column
            T.Columns(c).ColumnWidth = M.Columns(c).ColumnWidth
            Tbl.HeaderRowRange(c) = Master.HeaderRowRange(c)
            Tbl.ListColumns(c).DataBodyRange.NumberFormat = M(2, c).NumberFormat
'add formula if appropriate
            On Error Resume Next
                If M(2, c).HasFormula Then Tbl.ListColumns(c).DataBodyRange.Formula = M(2, c).Formula
                If Err.Number <> 0 Then GoTo Handling:
            On Error GoTo 0
            Exit For
        End If
    Next c
Exit Sub
Handling:
MsgBox "Unable to create formula " & vbCr & M(2, c).Formula & vbCr & "in sheet " & T.Parent.Name, vbExclamation, ""
End
End Sub
Private Sub DeleteColumn()
    For c = 2 To cT
        If WorksheetFunction.CountIf(Master.HeaderRowRange, T(1, c)) = 0 Then
            Tbl.ListColumns(c).Delete
            Exit For
        End If
    Next c
End Sub

Thank you very much for the great code and help.

Just as a side note to others who might want this to happen without interaction, this does require a manual RUN to update. I personally prefer it that way.

 

[LlebKcir]

I do have some questions on the code so I can add comments.

Private Master as ListObject, M as Range, cM as long

can that be also written as:

Private Master as ListObject
Private M as Range
Private cM as long

I am guessing that for InsertColumn() and DeleteColumn() the reason for starting the loop at c = 2 is to skip the first column as we talked about earlier in this thread?

 

[Yongle]

YES
- listing variables on separate lines (as you have done) is identical to putting them on one line each separated by a comma
- it's a matter of personal preference
- sometimes one way makes thing clearer than the other

YES
- that is why c starts with value = 2
- we decided to exclude column 1
- it has the benefit of making the code about a nanosecond faster
- it is possible the the code will work unaltered starting From c = 1 (but I did not test it)
- I was being a bit cautious at the outset!
- as can be seen in the line below, adding a column after the last column requires a different treatment

If c > cT Then Tbl.ListColumns.Add Else Tbl.ListColumns.Add Position:=c

 

[LlebKcir]

Yongle, if I change the starting value for c, will that lock in all columns before that value from the script?

Example I am thinking about locking down not from column 2, but column 7 onward. c=8 would do that trick?

 

[Yongle]

Yongle, if I change the starting value for c, will that lock in all columns before that value from the script?

you could test it for yourself and find out