title: Copy multiple specific sheets to new workbook
body: <p>I am using some legacy code to copy one specific worksheet (<code>Daily Summary</code>) to a new workbook and then replace all formulas with their actual values. This works fine for one sheet, but the code breaks once I try to set a <code>foreach</code> for a specified worksheet array with the sheet names (<code>&quot;Daily Summary&quot;,&quot;Daily Report&quot;</code>). Is there any specific syntax for copying multiple sheets?</p>
  <p>I did look at <a href="https://stackoverflow.com/questions/20903181/excel-vba-copying-multiple-sheets-into-new-workbook">this SO article</a> but am unable to run this code on Excel for MAC which sadly is required.</p>
  <p>Fairly new to Excel VBA, appreciate any guidance on this topic. Thank you.</p>
  <pre><code>'Copy the sheet to a new workbook
  Sheets(&quot;Daily Summary&quot;).Copy Before:=Sheets(1)
  
  With ActiveSheet
      .Cells.Copy
      .Cells.PasteSpecial xlPasteValues
  End With
  Application.CutCopyMode = False
  
  pctCompl = 10
  
  ActiveSheet.Copy
  Set Destwb = ActiveWorkbook
  
  
  
  'Determine the Excel version and file extension/format
  With Destwb
      If Val(Application.Version) &lt; 12 Then
          'You use Excel 2000-2003
          FileExtStr = &quot;.xls&quot;: FileFormatNum = -4143
      Else
          'You use Excel 2007-2010, we exit the sub when your answer is
          'NO in the security dialog that you only see  when you copy
          'an sheet from a xlsm file with macro's disabled.
          If Sourcewb.Name = .Name Then
              With Application
                  .ScreenUpdating = True
                  .EnableEvents = True
              End With
              MsgBox &quot;Your answer is NO in the security dialog&quot;
              Exit Sub
          Else
              Select Case Sourcewb.FileFormat
              Case 51: FileExtStr = &quot;.xlsx&quot;: FileFormatNum = 51
              Case 52:
                  If .HasVBProject Then
                      FileExtStr = &quot;.xlsm&quot;: FileFormatNum = 52
                      'FileExtStr = &quot;.pdf&quot;: FileFormatNum = 17
                  Else
                      FileExtStr = &quot;.xlsx&quot;: FileFormatNum = 51
                  End If
              Case 56: FileExtStr = &quot;.xls&quot;: FileFormatNum = 56
              Case Else: FileExtStr = &quot;.xlsb&quot;: FileFormatNum = 50
              End Select
          End If
      End If
  End With
  
  pctCompl = 30
  
  'Change all cells in the worksheet to values if you want
  'With Destwb.Sheets(1).Range(&quot;A1:I50&quot;)
  '    .Select
  '    .Copy
  '    .PasteSpecial xlPasteValues
  'End With
  'Application.CutCopyMode = False
  </code></pre>