title: VBA Code no longer recognizing worksheet after adding a second sheet
body: <p>I'm setting up an excel spreadsheet to track when weather stations are reporting for use in Tableau to create a dashboard so we can track the communication health of the network.</p>
  
  <p>This is being done through an API get that pulls the data every 10 minutes and then gets the difference between the timestamp on the data and a Now() function. If it is over 10 minutes it gets a 0 or if it is under it gets a 1 in Column H. My code is used to move those values from Column H to the right column associated with the right time period. And then on the top of the hour move what im calling the "HourScore" to the correct hour column which is then used to calculate the "DayScore" which is simply the percentage of times it accurately communicated in the entire day.</p>
  
  <p>It was all working great when I only was tracking one organization; now I'm tracking two and it stopped functioning after I added a second sheet for the other utility. </p>
  
  <p>I've tried a handful of different ways to get the object reference to work, but don't have a ton of experience with VBA so I'm unable to really pinpoint the exact problem.</p>
  
  <p>I've also tried separating them into different workbooks but they are both constantly running on a virtual server and I kept getting errors. That code looked very similar to what is below but each If function was split up.</p>
  
  <pre><code>Sub Minute_Save()
  ' Refresh Queries
  ActiveWorkbook.RefreshAll
  
  ' Creates a variable array from the pge_minscore Column
  Dim pge_ws As Worksheet
  Dim pge_minscore As Range
  Dim pge_hourscore As Range
  
  Dim sge_ws As Worksheet
  Dim sce_minscore As Range
  Dim sce_hourscore As Range
  
  Set pge_ws = Worksheets("PGE")
  Set pge_minscore = Range([E2], [E:E].Find("*", [E1], , , xlByRows, xlPrevious))
  
  Set sce_ws = Worksheets("SCE")
  Set sce_minscore = Range([E2], [E:E].Find("*", [E1], , , xlByRows, xlPrevious))
  
  ' Copy Data Refresh into MinuteTable for MinuteScore
  If Minute(Now()) &lt; 10 Then
      pge_ws.Range([H2], [H:H].Find("*", [H1], , , xlByRows, xlPrevious)).Resize(pge_minscore.Rows.Count, pge_minscore.Columns.Count).Cells.Value = pge_minscore.Cells.Value
      sce_ws.Range([H2], [H:H].Find("*", [H1], , , xlByRows, xlPrevious)).Resize(pge_minscore.Rows.Count, pge_minscore.Columns.Count).Cells.Value = pge_minscore.Cells.Value
  
  ElseIf Minute(Now()) &gt;= 10 And Minute(Now()) &lt; 20 Then
      pge_ws.Range([I2], [I:I].Find("*", [I1], , , xlByRows, xlPrevious)).Resize(pge_minscore.Rows.Count, pge_minscore.Columns.Count).Cells.Value = pge_minscore.Cells.Value
      sce_ws.Range([I2], [I:I].Find("*", [I1], , , xlByRows, xlPrevious)).Resize(pge_minscore.Rows.Count, pge_minscore.Columns.Count).Cells.Value = pge_minscore.Cells.Value
  
  ElseIf Minute(Now()) &gt;= 20 And Minute(Now()) &lt; 30 Then
      pge_ws.Range([J2], [J:J].Find("*", [J1], , , xlByRows, xlPrevious)).Resize(pge_minscore.Rows.Count, pge_minscore.Columns.Count).Cells.Value = pge_minscore.Cells.Value
      sce_ws.Range([J2], [J:J].Find("*", [J1], , , xlByRows, xlPrevious)).Resize(pge_minscore.Rows.Count, pge_minscore.Columns.Count).Cells.Value = pge_minscore.Cells.Value
  
  ElseIf Minute(Now()) &gt;= 30 And Minute(Now()) &lt; 40 Then
      pge_ws.Range([K2], [K:K].Find("*", [K1], , , xlByRows, xlPrevious)).Resize(pge_minscore.Rows.Count, pge_minscore.Columns.Count).Cells.Value = pge_minscore.Cells.Value
      sce_ws.Range([K2], [K:K].Find("*", [K1], , , xlByRows, xlPrevious)).Cells.Value = sce_minscore.Cells.Value
  
  ElseIf Minute(Now()) &gt;= 40 And Minute(Now()) &lt; 50 Then
      pge_ws.Range([L2], [L:L].Find("*", [L1], , , xlByRows, xlPrevious)).Resize(pge_minscore.Rows.Count, pge_minscore.Columns.Count).Cells.Value = pge_minscore.Cells.Value
      sce_ws.Range([L2], [L:L].Find("*", [L1], , , xlByRows, xlPrevious)).Resize(sce_minscore.Rows.Count, sce_minscore.Columns.Count).Cells.Value = sce_minscore.Cells.Value
  
  ElseIf Minute(Now()) &gt;= 50 Then
      pge_ws.Range([M2], [M:M].Find("*", [AC1], , , xlByRows, xlPrevious)).Resize(pge_minscore.Rows.Count, pge_minscore.Columns.Count).Cells.Value = pge_minscore.Cells.Value
      sce_ws.Range([M2], [M:M].Find("*", [M2], , , xlByRows, xlPrevious)).Resize(sce_minscore.Rows.Count, sce_minscore.Columns.Count).Cells.Value = sce_minscore.Cells.Value
  
      Set sce_hourscore = sce_ws.Range([N2], [N:N].Find("*", [N1], , , xlByRows, xlPrevious))
      Set pge_hourscore = pge_ws.Range([N2], [N:N].Find("*", [N1], , , xlByRows, xlPrevious))
  
          ' Copy MinuteScore to pge_hourscore
          If Hour(Now()) = 0 Then
              sce_ws.Range([O4], [O:O].Find("*", [O1], , , xlByRows, xlPrevious)).Resize(sce_hourscore.Rows.Count, sce_hourscore.Columns.Count).Cells.Value = sce_hourscore.Cells.Value
              pge_ws.Range([O4], [O:O].Find("*", [O1], , , xlByRows, xlPrevious)).Resize(sce_hourscore.Rows.Count, sce_hourscore.Columns.Count).Cells.Value = sce_hourscore.Cells.Value
        -some more code that is the same as above for more hours-  
         End If
      End If
  Application.OnTime Now + TimeValue("00:10:00"), "Minute_Save"
  End Sub
  </code></pre>
  
  <p>I receive:</p>
  
  <p>Run-time error '1004':
  Method 'Range' of object '_Worksheet' failed</p>
  
  <p>Following AJD's instructions I changed the above to the following code:</p>
  
  <pre><code>Set pge_ws = Worksheets("PGE")
  Set pge_minscore = pge_ws.Range("H2:H500")
  
  Set sce_ws = Worksheets("SCE")
  Set sce_minscore = sce_ws.Range([E2], [E:E].Find("*", [E1], , , xlByRows, xlPrevious))
  
  ' Copy Data Refresh into MinuteTable for MinuteScore
  If Minute(Now()) &lt; 10 Then
      'pge_ws.Range([H2], [H:H].Find("*", [H1], , , xlByRows, xlPrevious)).Resize(pge_minscore.Rows.Count, pge_minscore.Columns.Count).Cells.Value = pge_minscore.Cells.Value
      'sce_ws.Range([H2], [H:H].Find("*", [H1], , , xlByRows, xlPrevious)).Resize(pge_minscore.Rows.Count, pge_minscore.Columns.Count).Cells.Value = pge_minscore.Cells.Value
  
      Set startRange = pge_ws.Range("H2")
      Set endRange = pge_ws.Range("H:H").Find("*", [H1], , , xlByRows, xlPrevious)
      pge_ws.Range(startRange, endRange) = pge_minscore.Cells.Value
  </code></pre>
  
  <p>This worked; but I'm not entirely sure why. I should be able to adapt what ADJ gave me to the rest of it for my purposes. I think it is because I don't really fully understand the difference between values and ranges. Obviously I know ranges are a collection of cells but wouldn't that mean that ranges are just a collection of values? Either way it is beyond the scope of this post so thanks to the folks that helped out I appreciate you taking the time.</p>