The code get much more complicated if Code F is not on Sheet1. This macro
assume sheet1 contains all the codes and descriptions. Make sure worksheet
contains sheets 1 - 4. Add sheet 4 if it is missing.

Sub test()

'copy sheet1 to sheet4
Sheets("Sheet1").Cells.Copy _
Destination:=Sheets("Sheet4").Cells

'make header row on sheet4
With Sheets("Sheet4")
.Range("A1") = "CODE"
.Range("B1") = "DESCRIPTION"
.Range("C1") = "PERIOD1"
.Range("D1") = "PERIOD2"
.Range("E1") = "TOTAL"
End With

ShArray = Array("Sheet2", "Sheet3")

ColOff = 0
For Each wks In ShArray
With Sheets(wks)

LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
Set ShXColARange = .Range(.Cells(2, "A"), _
.Cells(LastRow, "A"))
Set ShXColBRange = .Range(.Cells(2, "B"), _
.Cells(LastRow, "B"))
End With
With Sheets("Sheet4")

LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh4ColXRange = .Range(.Cells(2, "C").Offset(0, ColOff), _
.Cells(LastRow, "C").Offset(0, ColOff))

For Each cell In Sh4ColXRange

code = Cells(cell.Row, "A").Value
code_total = WorksheetFunction.SumIf( _
ShXColARange, code, ShXColBRange)
If code_total < 0 Then
cell.Value = code_total
End If
Next cell
End With
ColOff = ColOff + 1
Next wks
With Sheets("Sheet4")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Cells(LastRow + 1, "A") = "TOTAL"
.Cells(LastRow + 1, "C").Formula = _
"=Sum(C2:C" & (LastRow) & ")"
.Cells(LastRow + 1, "D").Formula = _
"=Sum(D2:D" & (LastRow) & ")"
Set Sh4ColERange = .Range(.Cells(2, "E"), _
.Cells(LastRow, "E"))
For Each cell In Sh4ColERange
cell.Formula = _
"=Sum(C" & cell.Row & ":D" & cell.Row & ")"
Next cell
End With
End Sub


"u473" wrote:

Filtering the postings on this subject did not produce satisfying
results.

Three worksheets to start with :
1. Code Table
Code Desc
A Code A Desc
B Code B Desc
C Code C Desc
D Code D Desc
E Code E Desc

2. Period1 Data
Code Value Date
B 2 .....
B 6 .....
D 3 .....

3. Period2 Data
Code Value Date
A 5 .....
B 3 .....
C 7 .....
D 4 .....
F 8 ......

Desired Output on the 4th worksheet,
considering that the F code was not in the original Code Table,
implying prompting for creation on the fly during the looping
Assume all the WorkSheets are in the same Workbook

Resulting Summary Table

Code Description Period1 Period2 Total
A Code A Desc 5 5
B Code B Desc 8 3 11
C Code C Desc 7 7
D Code D Desc 3 4 7
E Code E Desc 0 0
F Code F Desc 8 8
T O T A L 11 27 38

Can you help me,
Thank you

Celeste