-2

I have a large dataset with the first column being the variable ClientNames. Some ClientNames have the word "Project" in and I'd like to use VBA to find and select each of the ClientNames which have the word "Project" in and move the entire row for these clients into a new workbook (one workbook with all the clients that have "project" in their ClientName. How can I do this?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
A_Pearson
  • 11
  • 1
  • If it is one time activity then use filter and then copy to new workbook. – Harun24hr Apr 07 '21 at 10:43
  • 2
    Welcome to stackoverflow :) I think you may have misunderstood how stackoverflow operates. You may want to see [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask) Please post what you have tried so far. Keep these in mind while posting **1.** What were you expecting? **2.** What is not working? Include Error message if applicable and we will take it from there :) – Siddharth Rout Apr 07 '21 at 10:48
  • @SiddharthRout Sorry yeah i'm new on here! i'll have a read and construct my question correctly next time :) – A_Pearson Apr 07 '21 at 15:54

2 Answers2

0

You can achieve that by using the below VBA code, the below code searches for text "project" in the first columns and then shifts the entire row to new workbook called Project

Option Explicit

Sub project()

Dim lastrow As Long
Dim count As Long
Dim sht As Worksheet
Set sht = ActiveSheet

Dim dst As Workbook
Set dst = Workbooks.Add
dst.SaveAs Filename:="Project"
dst.Sheets.Add.Name = "Project"
count = 1

sht.Activate
lastrow = sht.Cells(Rows.count, 1).End(xlUp).Row
Dim i As Long
    For i = 1 To lastrow
    
        If InStr(1, sht.Cells(i, 1).Value, "Project", vbTextCompare) > 0 Then
        sht.Rows(i).Cut
        dst.Sheets("Project").Rows(count).Insert Shift:=xlDown
        sht.Rows(i).Delete
        count = count + 1
        i = i - 1
        End If
    
    Next i
 End Sub
Ranga
  • 420
  • 2
  • 11
  • Ahh, now I got what you mean ^^ my bad you are right you're left with an empty row just the data gets cut out. • I'll remove my comments (and this one) to tidy up the answer and remove the confusing stuff! Feel free to do the same. – Pᴇʜ Apr 07 '21 at 13:53
  • Thanks, this code is exactly what i wanted, but Ranga when i run it, an error message comes up saying "You can't rearrange cells within a table this way, because it might affect other table cells in an unexpected way." Do you know why this is? When i press debug it highlights the line of code which is inserting the cut row and then shifting down a row. – A_Pearson Apr 07 '21 at 14:04
  • @A_Pearson that is because you copy the whole row and you have formatted tables (list objects) in your sheet. Either remove the formatted tables or handle them correctly as list objects [like I desrcibed here](https://stackoverflow.com/questions/43541445/select-entire-column-in-table-using-excel-vba/43541508#43541508). – Pᴇʜ Apr 07 '21 at 14:07
  • Thannks so much! :) @Pᴇʜ – A_Pearson Apr 07 '21 at 15:55
0

You can use InStr function to know if "Project" is in your cell. Then you can use entirerow to select your row then copy it to the destination sheet and finally delete the row from source sheet.
Be careful of your line index which will be need to be updated after deleting your row

DonKnacki
  • 427
  • 4
  • 11