StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POExcel macro to copy and paste data from one worksheet to another worksheet
text
Body
copied!<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" & CStr(LSearchRow)).Value) > 0 'If value in column E = "Mail Box", copy entire row to Sheet2 If Range("E" & CStr(LSearchRow)).Value = "Mail Box" Then 'Select row in Sheet1 to copy Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select Selection.Copy 'Paste row into Sheet2 in next row Sheets("MySheet").Select Rows(CStr(LCopyToRow) & ":" & 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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload