title: Subscript out of range when referencing a worksheet in another workbook from a variable
body: <p>See picture: <a href="http://s12.postimg.org/ov8djtuh9/Capture.jpg" rel="nofollow">http://s12.postimg.org/ov8djtuh9/Capture.jpg</a></p>
  
  <p><strong>Context:</strong> Trying to activate a sheet (variable: cSheet) in another workbook and paste data there from copied data from a different workbook. I'm getting a subscript out of range error whenever I try to activate directly using the variable (i.e. Worksheets(Name).Activate) or try to define a worksheet using the variable and then activate it. I've also tried other coding styles, using "With Worksheet" etc. and my code was a lot longer but I started over because every time I fix something, something else goes wrong. So, sticking to the basics. Any help would be greatly appreciated.</p>
  
  <pre><code>Sub GenSumRep()
  
  Dim AutoSR As Workbook
  Dim asrSheet As Worksheet
  Dim tempWB As Workbook
  Dim dataWB As Workbook
  Dim SecName As String
  Dim oldcell As String
  Dim nsName As String
  Dim cSheet As Worksheet
  
  Set AutoSR = ActiveWorkbook
  Set asrSheet = AutoSR.ActiveSheet
  
  For a = 3 To 10
  
      SecName = asrSheet.Range("D" &amp; a).Value
  
      If SecName &lt;&gt; "" Then
  
      Workbooks.Open Range("B" &amp; a).Value
      Set tempWB = ActiveWorkbook
      'tempWB.Windows(1).Visible = False
  
      AutoSR.Activate
  
      Workbooks.Open Range("C" &amp; a).Value
      Set dataWB = ActiveWorkbook
      'dataWB.Windows(1).Visible = False
  
      AutoSR.Activate
  
          'Copy paste data
          For b = 24 To 29
          oldcell = Range("C" &amp; b).Value
              If b = 24 Then
              nsName = Trim(SecName) &amp; " Data"
              Set cSheet = tempWB.Sheets(nsName)
              Else
              nsName = asrSheet.Range("B" &amp; b).Value
              Set cSheet = tempWB.Sheets(nsName)
              End If
  
          'Copy
          dataWB.Activate
          Range(oldcell).Select
          Range(Selection, Selection.End(xlToRight)).Select
          Range(Selection, Selection.End(xlDown)).Select
          Selection.Copy
  
          'Paste
          tempWB.Activate
          cSheet.Select
          Range("A1").Select
          Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
          Application.CutCopyMode = False
  
          b = b + 1
          Next b
  
      End If
  
  a = a + 1
  
  Next a
  
  End Sub
  </code></pre>