title: Copy contents of current worksheet to vba created worksheet
body: <p>I'm tearing my hair out trying to do what should be a really simple adjustment to a macro.</p>
  
  <p>copy and paste doesn't seem to work.  I get a property not supported error.</p>
  
  <p>All I am trying to do is copy all cell contents from the original activesheet in the originating workbook (which will be sName) and paste it to the new workbook sheet(rvname)</p>
  
  <p>Here is my current code: (I need it to work in excel 2003 and 2007)</p>
  
  <pre><code>Sub create_format_wb()
      'This macro will create a new workbook
      'Containing sheet1,(job plan) Original, (job plan) Revised, and 1 sheet for each task entered in the inputbox.
  
  
  Dim Newbook As Workbook
  Dim i As Integer
  Dim sName As String
  Dim umName As String
  Dim rvName As String
  Dim tBox As Integer
  Dim jobplannumber As String
  Dim oldwb As String
  
  
  line1:
  tBox = Application.InputBox(prompt:="Enter Number of Tasks", Type:=1)
  If tBox &lt; 1 Then
  MsgBox "Must be at least 1"
  GoTo line1
  Else
  
  sName = ActiveSheet.Name
  umName = (sName &amp; " Original")
  rvName = (sName &amp; " Revised")
  jobplannumber = sName
  
  
  
  
  Set Newbook = Workbooks.Add
      With Newbook
          .Title = sName
          .SaveAs Filename:=(sName &amp; " .xls")
          .Application.SheetsInNewWorkbook = 1
          .Sheets.Add(, After:=Sheets(Worksheets.Count)).Name = umName
          Worksheets(umName).Range("A1").Select
          With Worksheets(umName).QueryTables.Add(Connection:= _
              "ODBC;DSN=MX7PROD;Description=MX7PROD;APP=Microsoft Office 2003;WSID=USOXP-93BPBP1;DATABASE=MX7PROD;Trusted_Connection=Yes" _
              , Destination:=Range("A1"))
              .CommandText = Array( _
              "SELECT jobplan_print.taskid, jobplan_print.description, jobplan_print.critical" &amp; Chr(13) _
              &amp; "" &amp; Chr(10) &amp; "FROM MX7PROD.dbo.jobplan_print jobplan_print" &amp; Chr(13) &amp; "" &amp; Chr(10) _
              &amp; "WHERE (jobplan_print.jpnum= '" &amp; jobplannumber &amp; "' )")
              .Name = "Query from MX7PROD"
              .FieldNames = True
              .RowNumbers = False
              .FillAdjacentFormulas = False
              .PreserveFormatting = True
              .RefreshOnFileOpen = False
              .BackgroundQuery = True
              .RefreshStyle = xlInsertDeleteCells
              .SavePassword = False
              .SaveData = True
              .AdjustColumnWidth = True
              .RefreshPeriod = 0
              .PreserveColumnInfo = True
              .Refresh BackgroundQuery:=False
          End With
              .Worksheets(umName).UsedRange.Columns.AutoFit
              .Sheets.Add(, After:=Sheets(Worksheets.Count)).Name = rvName
              For i = 1 To tBox
                  .Sheets.Add(, After:=Sheets(Worksheets.Count)).Name = ("Task " &amp; i)
              Next i
      End With
  
  
      Worksheets(rvName).UsedRange.Columns.AutoFit
  End If
  End Sub
  </code></pre>
  
  <p>Can somone walk me through how to go about this?</p>
  
  <p>Any help appreciated.</p>