Pastebin launched a little side project called VERYVIRAL.com, check it out ;-) Want more features on Pastebin? Sign Up, it's FREE!
Guest

Untitled

By: a guest on Feb 22nd, 2014  |  syntax: None  |  size: 1.37 KB  |  views: 28  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. Sub copyRows()
  2.  
  3. Dim i As Integer, j As Integer, k As Integer
  4. Dim bReport As Workbook, bReport2 As Workbook
  5. Dim Report As Worksheet, Report2 As Worksheet
  6.  
  7. Set Report = Excel.ActiveSheet 'This assumes your sheet with data is the active sheet.
  8. Set bReport = Report.Parent
  9. Set bReport2 = Excel.Workbooks.Add 'This opens a new workbook
  10. Set Report2 = bReport2.Worksheets(1) 'This assigns the variable for the worksheet where we will be pasting our data.
  11.  
  12. Report2.Cells(1, 1).Value = "Copied Rows" 'This gives a title to the worksheet (helps to avoid having to identify _
  13.                                             the initial iteration of our for...next loop
  14.  
  15. For i = 1 To Report.UsedRange.Rows.Count 'Loops once for each row in the worksheet.
  16.  
  17.     If InStr(1, Left(Report.Cells(i, 2).Value, 1), "e", vbTextCompare) = 1 Then 'This searches the first letter of the value. I used the instr function to make it case-insensitive.
  18.                                                                                     'You could also disable the binary comparison for the subroutine to accomplish this, but why bother?
  19.         Report.Cells(i, 1).EntireRow.Copy 'This copies the row
  20.         j = Report2.UsedRange.Rows.Count + 1 'This finds the row below the last used row in the new worksheet/workbook.
  21.         Report2.Cells(j, 1).EntireRow.Insert (xlDown) 'This pastes the copied rows.
  22.     End If
  23. Next i
  24.  
  25. End Sub