Add Column to Excel Table (VBA) but only 1st row populates not entire row

[shell_l_d]

I have an Excel spreadsheet with Macros/VBA:
* runs several SQL queries from an external database
* populates worksheet(s) with SQL result(s)
* in each worksheet, select data & create a table

* add extra columns to most of these tables
* add calculated formulas to first row of these extra columns (which auto populates the entire row with the same formula)

The problem is that at least 2 of our South African employees whom run this Excel spreadsheet, don't get the auto population of the entire row of the tables extra columns, so only the first row is populated (eg: D1=header & D2=1st data row, D3 to D5=blank data rows, D6=total).

They are using Windows XP (V5.1) & MS Office Professional 2007.

Any ideas what could cause this please & how can I fix it?

Code extracts to create table:

         Dim iLastCol As Integer, iLastRow As Integer, iPos  As Integer, iMaxWidth As Integer
         Dim sEndTable As String, sLastColRef As String,  sTblName As String
         Dim oRngCol As Object
 
8         With ActiveSheet
 
9             .Range("A2").Select
10            sTblName = "tbl" & .Name
 
             ' find position of entire table including  headings
12            iLastCol =  .Range("A1").End(xlToRight).Column
13            iLastRow = 1    ' default in case no  data
 
             ' Ignore possible Error '6 Overflow' if only  header exists (no data)
14            On Error Resume Next
15            iLastRow =  .Range("A1").End(xlDown).Row
16            On Error GoTo  Error_In_CreateTable
 
             ' extract the column letter from sEndTable  eg:  AB from "$AB$100"
17            sEndTable = .Cells(iLastRow,  iLastCol).Address
18            iPos = VBA.InStrRev(sEndTable, "$",  -1)
19            sLastColRef = VBA.Mid(sEndTable, 2, iPos -  2)
 
             ' Add table - ignore error if table already  exists
20            On Error Resume Next
21            .ListObjects.Add(xlSrcRange, Range("$A$1:"  & sEndTable), , xlYes).Name = sTblName
22            On Error GoTo  Error_In_CreateTable
 
             ' Tablestyle for new table (adds filtering,  colours & totals)
23            With .ListObjects(sTblName)
24                .TableStyle = "TableStyleMedium9"  '  blue
25                .ShowHeaders = True
26                .ShowTotals = True
27            End With
 
47        End With               
     ' ===== Exit Handler =====
Exit_CreateTable:
48        On Error Resume Next
         ' Release memory used by Objects
49        If Not oRngCol Is Nothing Then oRngCol =  Nothing
50        Exit Sub

Code extracts to add extra columns to table:

  Dim iRow As Integer
        Dim iCol As  Integer
 
2         With  ThisWorkbook.Worksheets("WaitOnCust")
3              .Select
4              Range("tblWaitOnCust").Select
 
5             iRow =  1
6             iCol =  .Range("tblWaitOnCust").Columns.Count
 
            '  Headings
8             .Cells(iRow, iCol  + 1).Value = "Event Day"
9             .Cells(iRow, iCol  + 2).Value = "Change from Wait"
10            .Cells(iRow, iCol  + 3).Value = "Calc Wait"
11            .Cells(iRow, iCol  + 4).Value = "Calc Wait Final"
 
            '  Values
12            On Error Resume  Next
13            .Cells(iRow + 1,  iCol + 1).FormulaR1C1 = "=WEEKDAY(tblWaitOnCust[[#This Row],[Event  Date]],2)"              
16            .Cells(iRow + 1,  iCol + 4).FormulaR1C1 = "=IF(tblWaitOnCust[[#This Row],[Calc  Wait]]<0,0,tblWaitOnCust[[#This Row],[Calc Wait]])"
17            On Error GoTo  Error_In_ExtraWaitOnCust
 
            ' Totals  row
22             Range("tblWaitOnCust[[#Totals],[Calc Wait]]").FormulaR1C1 =  "=SUBTOTAL(109,[Calc Wait])"
23             Range("tblWaitOnCust[[#Totals],[Calc Wait Final]]").FormulaR1C1 =  "=SUBTOTAL(109,[Calc Wait Final])"
 
25        End  With
 
        ' ===== Exit Handler  =====
26        Exit  Sub

 

[Andrew Poulsom]

Have your users disabled the autofill functionality? It's under Office Button|Excel Options| Proofing|AutoCorrect Options|Fill formulas in tables to create calculated columns.

 

[shell_l_d]

Thanks so much Andrew, that was the problem.