title: Excel vba how to copy sheet with all formatting & page setup
body: <p>I've seen quite a few examples for making a full copy of a worksheet but none of them are working for me. In my case the sheet has to go into a new workbook. In my actual code wb is defined global and the workbook is created in another sub that called this one.  </p>
  
  <pre><code>  Dim wb As Workbook
    Set wb = Workbooks.Add()
    Dim newtab as Worksheet
  
    With ActiveWorkbook
     .Sheets("Sample Attendance").Copy After:=wb.Sheets(.Sheets.Count)
     Set newtab = wb.Sheets(wb.Sheets.Count - 1)
    End With
  </code></pre>
  
  <p>didn't work.</p>
  
  <p>Likewise</p>
  
  <pre><code>  ActiveWorkbook.Sheets("Sample Attendance").Copy After:=wb.Sheets(1)
    Set newtab = wb.Sheets("Sample Attendance")
    newtab.Name = tabname
  </code></pre>
  
  <p>both methods return after the Copy statement.  </p>
  
  <p>I've been moderately successful with this:</p>
  
  <pre><code>  Set newtab = wb.Worksheets.Add
    newtab.Name = tabname
    Set Attendance = ThisWorkbook.Sheets("Sample Attendance")
    Attendance.Range("A:BB").Copy Destination:=newtab.Cells(1, 1)
  </code></pre>
  
  <p>which works. But then I have to copy all of the PageSetup across which is giving me fits and takes forever.</p>