Auto fill "cell values against each "worksheet name" in Summary Sheet" to a cell location

Just guessing without sample workbook:

sub test()
dim cell as range
sheets("Summary").activate
  for each cell in range("B1:B" & cells(rows.count, "B").end(xlup).row)
    cell.offset(0,1).value = sheets(cell.value).range("D3").value
  next cell
end sub

so probably:

sub test2()
dim cell as range
sheets("Summary").activate
  for each cell in range("B2:B" & cells(rows.count, "B").end(xlup).row) 'in B1 you have a header
    sheets(cell.value).range("D3").value = cell.offset(0,3).value  'offset 0,3 because column E is 3 columns right from B
  next cell
end sub

Note that the image you posted is frankly speaking: useless
If it had column letters and row numbers would be a bit better (I'm not sure if the value is indeed in column E - you have shown 4 columns so rather A:D - if so use offset(0,2))
But appropriate attachment for this forum is a workbook. Can contain dummy data but the structure and formatting shall be the same as real data.