title: Reference a newly opened Workbook to run code on it
body: <p>I know I can open a workbook and have code run in the same workbook if the mapping to the file is pasted into the macro code.</p>
  
  <p>I want to use an open dialogue box to select it.</p>
  
  <p>This code:</p>
  
  <pre><code>Option Explicit
  Private Sub CommandButton1_Click()
  
  Dim directory As String, fileName As String, sheet As Worksheet, total As Integer, fd As Office.FileDialog
  
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  
  With fd
  
      .AllowMultiSelect = False
  
      .Title = "Please select the file."
  
      .Filters.Clear
      .Filters.Add "Excel 2003", "*.xls?"
  
      If .Show = True Then
          fileName = Dir(.SelectedItems(1))
      End If
  End With
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Workbooks.Open (fileName)
  
  For Each sheet In Workbooks(fileName).Worksheets
      total = Workbooks("import-sheets.xlsm").Worksheets.Count
      Workbooks(fileName).Worksheets(sheet.Name).Copy _
      after:=Workbooks("import-sheets.xlsm").Worksheets(total)
  Next sheet
  
  Workbooks(fileName).Close
  
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
  </code></pre>
  
  <p>allows me to select the file through an open dialogue box but then the workbook opens in a new window.</p>