find and move

[tourless]

Hi Folks.

I'm working on automating a solution to a problem file and I need to extract some rows to a new sheet programmatically. Specifically I'm looking for a way to loop through column H and any cells that begin with the word 'Net' (spelled exactly like that) need to be cut out and moved to a new workbook. Then those now blank rows need to be deleted on my source sheet. I can loop through my Column H and identify and manipulate the rows that begin with my specified value, but I have no idea how to move them and then remove the blank rows. I need to say (in vba language) start at cell H4, if the value in that cell begins with 'Net', cut and paste it to a new workbook (sheet1), now go back and remove the blank row and start again but remember to account for the row that used to be below the row I was just working with. Of course I don't want the first instance overwritten in my destination sheet so that needs to be taken into account.

There might be a more efficient way of accomplishing this but this is my current thought process. Also note that all instances H rows that begin with 'Net' will be sequential, not randomly disbursed throughout my rows. Not sure if that makes a difference but I thought it worth mentioning.

Thanks for your time and knowledge.

 

[Fluff]

How about

Sub tourless()
   Dim Wbk As Workbook
   With ActiveSheet
      .Range("A1:H1").AutoFilter 8, "Net*"
      Set Wbk = Workbooks.Add
      .AutoFilter.Range.EntireRow.Copy Wbk.Sheets(1).Range("A1")
      .AutoFilterMode = False
   End With
End Sub

 

[tourless]

How about

Sub tourless()
   Dim Wbk As Workbook
   With ActiveSheet
      .Range("A1:H1").AutoFilter 8, "Net*"
      Set Wbk = Workbooks.Add
      .AutoFilter.Range.EntireRow.Copy Wbk.Sheets(1).Range("A1")
      .AutoFilterMode = False
   End With
End Sub

How about perfect. I didn't think about using a filter. Well done sir! Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.