Formula/ Macro that will copy and paste

[Randy]

I am creating a number of spreadsheets that have multiple links with each
other. One link I am trying to make is this: Condition: If column L has text
"NSI", then copy and paste entire row to a separate worksheet. However, I
need it to paste in a sequential order begining with row 2 of the new sheet
and not in the same row it is copied from on the new sheet. I am probably on
the bottom end of the advanced scale of Excel and would greatly appreciate
any assistance anyone can offer. Thank you.

 

[Mike H]

Hi,

Alt + F11 to open VB editor. right click 'This workbook' insert module and
paste this in and run it.

Sub stance()
Dim myrange
Dim copyrange As Range
Sheets("Sheet1").Select
Lastrow = Cells(Cells.Rows.Count, "L").End(xlUp).Row
Set myrange = Range("L1:L" & Lastrow)
For Each C In myrange
If UCase(C.Value) = "NSI" Then
If copyrange Is Nothing Then
Set copyrange = C.EntireRow
Else
Set copyrange = Union(copyrange, C.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Copy
End If
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
End Sub

Mike