Ok, I'll start diddling with the code to make it work for this.  More
questions.
1.  You say no more periods will be added, BUT there will be 12.  Does this
mean you want to only show the additional periods on the combined table as
they are populated?
2.  Do the tables have 2 heading rows, such as
Code Table
Code  Descr
in rows 1 and 2, with data beginning in row 3 and
Period1 Data
Code  Value  Date
in rows 1 and 2, with data beginning in row 3, etc.?
James

Hi,

I decided to take this on and it became a marathon. However, try what I have
and see how it goes.

I'm sure that I shouldn't have to tell you this but I will anyway. Make sure
that you have a backup of your workbook.

I have assumed that your code table all of your data column headers start at
cell A1 on each sheet.

Also assumes that worksheet 'Summary' exists.

You will need to rename your data sheets to just Period1, Period2, Period3
etc. This is because I made it dynamic for the number of data sheets and I
have used the sheet names on the Summary. I didn't want to start extracting
part of the name because when you get past Period9 there is more characters
etc and I am sure you understand.

Run the macro from Sub Summary_Data and it calls the second procedure.

Also it adds a worksheet called Temp. You can either leave it in or delete
it if you don't want it.

For the missing codes, it requests that you enter them. If no entry then the
procedure terminates.

Missing codes are appended to the codes in Code Table.

Option Explicit
Dim wsCode As Worksheet 'Code Table W'sheet
Dim rngCode As Range    'Code Table code Column
Dim rngData As Range    'Data in Data W'Sheets
Dim ws As Worksheet     'Each data W'sheet
Dim wsSumm As Worksheet 'Summary W'sheet
Dim rngSummCode As Range 'Codes in summary W'sheet
Dim cCode As Range      'Each cell in rngCode
Dim dCode As Range      'Each cell in rngData
Dim colNumb As Single   'Column # for summary headers
Dim colName As String   'Summary W'Sht Column names
Dim cellFound As Range  'Found cell in summary W'Sheet
Dim rngHeadSumm As Range 'Column headers in Summary W'Sht
Dim cHead As Range      'Each col header in Summary W'Sht
Dim rngSelect   'Selected range
Dim rngCodeDescr    'Range of descriptions
Dim c As Range  'each cell in rngSelect
Dim strTemp 'Code holder
Dim strInput    'Input box data
Dim rowNumb     'No of rows of data in summary

Sub Summary_Data()

Set wsCode = Worksheets("Code Table")

'Update Code Table from existing Code Table
'plus any new codes found in data.
Call Temp_Code_Table

With wsCode
   Set rngCode = Range(.Cells(2, 1), _
       .Cells(Rows.Count, 1).End(xlUp))
End With

'Create Summary sheet column headers from data sht names
Set wsSumm = Sheets("Summary")

With wsSumm
   .Cells.Clear
   .Cells(1, 1) = "Code"
   .Cells(1, 2) = "Description"
   colNumb = 2 'Initialize column number for headers
   For Each ws In Worksheets
       If Left(ws.Name, 6) = "Period" Then
           colNumb = colNumb + 1
           colName = ws.Name
           .Cells(1, colNumb) = colName 'Col Head = Sht name
       End If
   Next ws
   colNumb = colNumb + 1
   .Cells(1, colNumb) = "Total"
   Set rngHeadSumm = Range(.Cells(1, 1), _
       .Cells(1, Columns.Count).End(xlToLeft))
End With

Sheets("Temp").Select
Range(Cells(2, 2), Cells(Rows.Count, 3).End(xlUp)).Copy _
   Destination:=Sheets("Summary").Range("A2")
   

For Each cCode In rngCode
   For Each ws In Worksheets
       If Left(ws.Name, 6) = "Period" Then 'Is data sheet
           With ws
               Set rngData = Range(.Cells(2, 1), _
                   .Cells(Rows.Count, 1).End(xlUp))
           End With
           'Find column number matching data sht name
           For Each cHead In rngHeadSumm
               If cHead = ws.Name Then
                   colNumb = cHead.Column
                   Exit For
               End If
           Next cHead
         
           ws.Select
           rngData.Select
           
           For Each dCode In rngData
             
   If dCode = cCode Then   'Found in code table
       'Find Summary col numb = data sht name
       With wsSumm
           Set rngSummCode = Range(.Cells(2, 1), _
               .Cells(Rows.Count, 1).End(xlUp))
       Set cellFound = rngSummCode.Find(What:=dCode, _
               LookIn:=xlFormulas, _
               LookAt:=xlWhole, _
               SearchOrder:=xlByColumns, _
               SearchDirection:=xlNext, _
               MatchCase:=False, _
               SearchFormat:=False)
           If Not cellFound Is Nothing Then    'Found
               .Cells(cellFound.Row, colNumb) = _
                   .Cells(cellFound.Row, colNumb) _
                   + dCode.Offset(0, 1)
           End If
       End With
       
   End If
           Next dCode
         
       End If
   Next ws
Next cCode

'Insert formulas for Totals
Sheets("Summary").Select
rowNumb = Cells(Rows.Count, 1).End(xlUp).Row
Rows("1:1").Select
Selection.Find(What:="Total", _
   After:=ActiveCell, _
   LookIn:=xlFormulas, _
   LookAt:=xlWhole, _
   SearchOrder:=xlByRows, _
   SearchDirection:=xlNext, _
   MatchCase:=False, _
   SearchFormat:=False).Activate

'Insert row totals
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC3:RC[-1])"

ActiveCell.Copy _
   Destination:=Range(ActiveCell, _
   Cells(rowNumb, ActiveCell.Column))

'Insert column totals
Cells(rowNumb, ActiveCell.Column).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R2C:R[-1]C)"

ActiveCell.Copy _
   Destination:=Range(ActiveCell, _
   Cells(rowNumb + 1, 3))
   
Cells(rowNumb + 1, 1) = "Totals"

Cells.Columns.AutoFit

End Sub

Sub Temp_Code_Table()

On Error Resume Next
Sheets("Temp").Select
On Error GoTo 0

'If sheet temp not already exists then add
If ActiveSheet.Name <> "Temp" Then
   Sheets.Add After:=Sheets(Sheets.Count)
   ActiveSheet.Name = "Temp"
End If
Cells.Clear
Range("A1") = "Code"

wsCode.Select
Application.CutCopyMode = False
Range("B1").Select
Selection.End(xlDown).Select
ActiveWorkbook.Names.Add Name:="Last_Descript", _
   RefersToR1C1:=ActiveCell

Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp)).Copy _
   Destination:=Sheets("Temp").Range("A2")
   
For Each ws In Worksheets
   If Left(ws.Name, 6) = "Period" Then 'Is data sheet
       ws.Select
       Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp)).Copy _
       Destination:=Sheets("Temp").Cells(Rows.Count, 1) _
           .End(xlUp).Offset(1, 0)
   End If
Next ws

Sheets("Temp").Select
Range("A1").Select

Set rngSelect = Range(Selection, Selection.End(xlDown))

rngSelect.AdvancedFilter Action:=xlFilterCopy, _
   CopyToRange:=Range("B1"), _
   Unique:=True

Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'Code Table'!R2C1:Last_Descript,2,FALSE)"

Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste

Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
   Operation:=xlNone, SkipBlanks _
   :=False, Transpose:=False
Application.CutCopyMode = False

Set rngCodeDescr = Range(Cells(2, 3), _
   Cells(Rows.Count, 3).End(xlUp))
rngCodeDescr.Select

For Each c In rngCodeDescr
   
   If IsError(c) Then
       
       strTemp = c.Offset(0, -1)
       strInput = InputBox("Code " & strTemp & _
           " does not have a description " & Chr(10) _
           & "Please insert the description")
       If strInput = "" Then
           MsgBox "Description not entered" & _
           Chr(13) & "Processing terminated"
           End
       End If
       c.Value = strInput
       'Add to code Table
       Range(c.Offset(0, -1), c).Copy _
           Destination:=Sheets("Code Table"). _
           Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
   End If
Next c
End Sub

Hope it works well for you,

Regards,

OssieMac