title: Excel macro to copy and paste data from one worksheet to another worksheet
body: <p>I am trying to Search for a value in a column and copy row  from Sheet1 and creating new sheet as MySheet and pasting that particular row .But I am getting run time error while pasting data in  MySheet.Any suggestions please.   </p>
  
  <p>Data Input I am trying :</p>
  
  <p>ID  name    price  units  desc</p>
  
  <p>1   ikura       10     4      Mail Box</p>
  
  <p>2   test        11     14     xxxx</p>
  
  <p>3   test        11     14     yyyy</p>
  
  <p>4   test        11     14     Mail Box</p>
  
  <pre><code> Sub SearchForString()
  
          Dim LSearchRow As Integer
          Dim LCopyToRow As Integer
  
          On Error GoTo Err_Execute
  
          'Start search in row 4
          LSearchRow = 4
  
          'Start copying data to row 2 in Sheet2 (row counter variable)
          LCopyToRow = 2
  
      Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = "MySheet"
          While Len(Range("A" &amp; CStr(LSearchRow)).Value) &gt; 0
  
              'If value in column E = "Mail Box", copy entire row to Sheet2
              If Range("E" &amp; CStr(LSearchRow)).Value = "Mail Box" Then
  
                  'Select row in Sheet1 to copy
                  Rows(CStr(LSearchRow) &amp; ":" &amp; CStr(LSearchRow)).Select
                  Selection.Copy
  
                  'Paste row into Sheet2 in next row
                  Sheets("MySheet").Select
                  Rows(CStr(LCopyToRow) &amp; ":" &amp; CStr(LCopyToRow)).Select
                  ActiveSheet.Paste
  
                  'Move counter to next row
                  LCopyToRow = LCopyToRow + 1
  
                  'Go back to Sheet1 to continue searching
                  Sheets("Sheet1").Select
  
              End If
  
              LSearchRow = LSearchRow + 1
  
          Wend
  
          'Position on cell A3
          Application.CutCopyMode = False
          Range("A3").Select
  
          MsgBox "All matching data has been copied."
  
          Exit Sub
  
      Err_Execute:
          MsgBox "An error occurred."
  
      End Sub
  </code></pre>
  
  <p>Regards,</p>
  
  <p>Raju</p>