title: Copy multiple sheets to a different workbook
body: <p>The following code is supposed to copy all worksheets (except last 2) from workbook called PCReport to a new workbook called Insp &amp; date.</p>
  
  <p>It falls over on the line</p>
  
  <pre><code>Workbooks(xWkb).Sheets(sheet.Name).Copy after:=Workbooks(tgtWkb).Sheets(x)
  </code></pre>
  
  <p>The error is runtime error 424, object required.</p>
  
  <p>x = 1, total = 10, xWkb = "PCReport.xlsm", tgtWkb = "Insp25112015.xls"</p>
  
  <pre><code>Dim total As Integer
  Dim NewWkb As Workbook
  Dim xWs As Worksheet
  Dim xWkb As String
  Dim tgtWkb As String
  Dim i As Integer
  
  xWkb = "PCReport.xlsm"
  Set NewWkb = Workbooks.Add
  'ActiveWorkbook.SaveAs "C:\Users\Carol\Desktop\Insp" &amp; Format(Date, "ddmmyyyy") &amp; ".xls"
  ActiveWorkbook.SaveAs "C:\Users\Carol.Hedges\Insp" &amp; Format(Date, "ddmmyyyy") &amp; ".xls"
  tgtWkb = ActiveWorkbook.Name
  total = Workbooks(xWkb).Worksheets.Count
  i = 1
  For x = 1 To total - 2
      Workbooks(xWkb).Sheets(sheet.Name).Copy after:=Workbooks(tgtWkb).Sheets(x)
      i = i + 1
  Next
  </code></pre>