title: Storing a worksheet name for use in another sub
body: <p>I'm using one sub-procedure to store filepaths/workbooks and worksheets in Public variables which can then be accessed by other sub procedures in the module. Here is some example code:</p>
  
  <pre><code>Public myfp As String
  Public mywb As Workbook
  Public myws As Worksheet
  
  Sub FilePaths()
      myfp = Application.GetOpenFileName(Title:="Please select the file.")
      Set mywb = Workbooks.Open(myfp, 0)
      If Left(mywb.Sheets(1).Name, 2) = "01" Then
          Set myws = mywb.Sheets(1)
      Else
          Set myws = mywb.Sheets(2)
      End If
      mywb.Saved = True
      mywb.Close
  Exit Sub
  
  Sub Stuff()
      Dim wb As WorkBook
      Dim ws As Worksheet
      Set wb = ActiveWorkbook
      Set ws = wb.Sheets(1)
      ws.Range("A1").Value = wb.ws.Range("A1").Value
  End Sub
  </code></pre>
  
  <p>So my first sub determines which sheet I need and my second sub should put the value of this sheets cell into my sheet. However all I get is errors. I figure this may be because I have to open the file so I tried something I know should work:</p>
  
  <pre><code>SUb Stuff()
      Range("A1").Formula = "='[" &amp; myfp &amp; "]" &amp; myws &amp; "'!A1"
  End Sub
  </code></pre>
  
  <p>However this also doesn't work. What am I doing wrong?</p>