My workbook has 12 sheets each having the name of a month. Each sheet contains a table named LotSizexy where xy = number of the respective month as given below.
Sheet name-Table name: January-LotSize01, February-LotSize02,...., December-LotSize12.
Each of these tables have two columns named SCRIP and Lot Size. I want a simple VBA code for following:
1] Clear the content of Lot Size column of each such table from all 12 sheets.
2] Add text A, B, C, D, E in the cells of column SCRIP of each such table in all 12 sheets.
Try this.
It will clear all data from the tables and add rows with A, B, C etc.
It assumes there is only one table on each of the monthly sheets.
Sub UpdateTables()
Dim monthNames As Variant: monthNames = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
Dim vals As Variant: vals = Array("A", "B", "C", "D", "E")
Dim ws As Worksheet
Dim tbl As ListObject
Dim lr As ListRow
Dim i As Long
Dim j As Integer
For i = LBound(monthNames) To UBound(monthNames)
Set ws = ThisWorkbook.Worksheets(monthNames(i))
If Not ws Is Nothing Then
If ws.ListObjects.Count > 0 Then
Set tbl = ws.ListObjects(1)
With tbl
If Not .DataBodyRange Is Nothing Then .DataBodyRange.Delete
For j = 1 To 5
Set lr = .ListRows.Add
lr.Range(tbl.ListColumns("SCRIP").Index) = vals(j - 1)
Next j
End With
End If
End If
Set ws = Nothing
Set tbl = Nothing
Set lr = Nothing
Next i
End Sub
@BadlySpelledBuoy. Thanks for the answer but I am having three different tables on each sheet with LotSize?? (?? = Month number) being one of them. How can we modify your given code to incorporate this change so that the code operates on the desired table to achieve the stated objectives?
Difficult to know for sure without seeing an example file, but give this a try.
Option Explicit
Sub UpdateTables()
Dim monthNames As Variant: monthNames = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
Dim vals As Variant: vals = Array("A", "B", "C", "D", "E")
Dim ws As Worksheet
Dim tbl As ListObject
Dim lr As ListRow
Dim i As Long
Dim j As Integer
For i = LBound(monthNames) To UBound(monthNames)
Set ws = ThisWorkbook.Worksheets(monthNames(i))
If Not ws Is Nothing Then
If ws.ListObjects.Count > 0 Then
For Each tbl In ws.ListObjects
Set tbl = ws.ListObjects(1)
With tbl
If Not .DataBodyRange Is Nothing Then .DataBodyRange.Delete
For j = 1 To 5
Set lr = .ListRows.Add
lr.Range(tbl.ListColumns("SCRIP").Index) = vals(j - 1)
Next j
End With
Next tbl
End If
End If
Set ws = Nothing
Set tbl = Nothing
Set lr = Nothing
Next i
End Sub