title: Sum values across worksheets
body: <p>I have a workbook which is a weekly report. By the end of the week it will contains eight worksheets, one for each day of the week and an 8th which reports the totals for the whole week.</p>
  
  <p>Worksheets:</p>
  
  <pre><code>Mon, Tue, Wed, Thur, Fri, Sat, Sun, Week
  </code></pre>
  
  <p>Each worksheet has the same format copied from a template sheet.</p>
  
  <p>For the sake of keeping things simple I've boiled this down to one cell B12.</p>
  
  <p><code>B12</code> on the worksheeet <code>"Week"</code> needs to equal:</p>
  
  <pre><code>=Mon!B12+Tue!B12+Wed!B12+Thur!B12+Fri!B12+Sat!B12+Sun!B12
  </code></pre>
  
  <p>I was going to use VBA to insert the above formula into Cell <code>B12</code> of the worksheet "Week" however I have a couple of issues:</p>
  
  <p>1) If I run the report on Wednesday only <code>"Mon"</code> and <code>"Tue"</code> will exist. If I run the report on Saturday, <code>"Mon", "Tue", "Wed", "Thur"</code> and <code>"Fri"</code> will exist. This means I can't just insert the above formula into cell <code>B12</code> of "Week" because I get a <code>#REF!</code> error due to sheets that don't exist.</p>
  
  <p>I need to add up all the <code>B12</code> values of any of the seven daily sheets but only if that sheet exists.</p>
  
  <p>So if I run the report on Wednesday it equals:</p>
  
  <pre><code>=Mon!B12+Tue!B12
  </code></pre>
  
  <p>If I run the report on Saturday it equals:</p>
  
  <pre><code>=Mon!B12+Tue!B12+Wed!B12+Thur!B12+Fri!B12
  </code></pre>
  
  <p>2) This isn't applicable just to cell <code>B12</code> but to about 100 cells and they are not all in one range, they are spread out and some are merged cells etc. I want to apply a solution to many other cells without having to write out hundreds of if statements etc.</p>
  
  <p>This is where I've got to so far but it doesn't work:</p>
  
  <pre><code>Sub test()
  
  Dim ws As Worksheet
  Dim testVar As Double
  Dim wsName As String
  wsName = ws.Name
  For Each ws In ActiveWorkbook.Worksheets
  Select Case wsName
      Case "Mon", "Tue", "Wed", "Thur", "Fri", "Sat", "Sun"
          testVar = testVar + Worksheets(wsName).Range("B12").Value
  End Select
  Next ws
  
  Worksheets("Week").Range("B12").Value = testVar
  
  End Sub
  </code></pre>