title: Referring to Workbook/Worksheet with variable names
body: <pre><code>Sub CpyProdSch()
  Dim wbkOpen As Workbook    
  Dim wb2 As Workbook    
  Dim wsName As String    
  Dim strFileName As String    
  Dim strFilePath As String    
  Dim MsgBoxResult As Long    
  
  strFilePath = "\\Ykf001\grpdata\PUBLIC\Operations\Converting Schedule\"  
  
  ' this could also be a constant
  strFileName = Dir(strFilePath)
  
  
  Set wbkOpen = Workbooks.Open(strFilePath &amp; strFileName, False, True)
  
  'It stops here - or at least it appears to - defaults back to VBA view and does nothing further
  
      ActiveWorkbook.ActiveSheet.Range("A1:BN500").Select
          Selection.Copy
  
      Workbooks("Raw Data pRODUCTION.xlsx").Worksheets("production    Data").Range("A1:C1").Select.PasteSpecial Paste:=xlPasteValues, _
           Operation:=xlNone, SkipBlanks:=True, Transpose:=False
      Application.CutCopyMode = False
  
  End If
  wbkOpen.Close False
  
  Application.ScreenUpdating = True
  </code></pre>
  
  <p>As you can see I have a variable workbook name, as the only way I can refer to it is knowing its the only excel file in the folder I'm accessing - so this isn't as simple as activating a known workbook name. </p>
  
  <p>As it stands now this portion of the code OPENS the workbook fine, it just won't do anything in the workbook because I think I'm referring to it incorrectly.</p>
  
  <p>Additionally, the sheet name with the data changes:
  Currently it says August 29, before it says July 14, its variable but it is always the first sheet. For some reason when the workbook opens though it opens to the second sheet.</p>
  
  <p>I need to activate the first sheet in the workbook I just opened so that I can copy and paste a range into my "Raw Production Data" workbook.</p>
  
  <hr>
  
  <p>Here is the code now [code]     Set wbkOpen = Workbooks.Open(strFilePath &amp; strFileName, False, True)
          Sheets(1).Activate
          Set wbkOpen = ActiveWorkbook</p>
  
  <pre><code>    wbkOpen.Sheets(1).Range("A2").Copy
  
       Workbooks("Raw Data pRODUCTION.xlsx").Worksheets("Production Data").Range("A1").Select.PasteSpecial Paste:=xlPasteValues, _
              Operation:=xlNone, SkipBlanks:=True, Transpose:=False
              Application.CutCopyMode = False
     [/code]
  </code></pre>
  
  <p>It still just opens the workbook and stops. I trimmed it down to just select sheets(1), and nothing happens after it opens the book. And the code WAS working this morning. I am baffled.</p>
  
  <hr>
  
  <p>I did some tinkering so this is odd.</p>
  
  <p>No matter what the file path is, where the sub is, or what the excel workbook is every single time the workbook will open then the macro will stop.</p>
  
  <p>It's completely irrelevant of the action, because once that file opens the macro just stops.</p>
  
  <p>Any ideas?</p>