copy/paste row based on keyword

[MikeWip]

Hi guys,

wanna know if you guys could help me write vba code.
I have a file with 2 tabs. Tab 1: London Vigs Tab2: FILE
Ideally, I'd like all rows from tab 1 containing the wording: (DONE) in column A, to be copy/pasted onto tab 2 (FILE)

for instance

A1: car (DONE)
A2: bike (DONE)
A3: cylce
A4: bus

only row 1 & 2 should be copy/pasted onto tab2 because of the wording (DONE). data from row 3 & 4 are not copy/pasted.

thanks for your help dudes

Mike

 

[Fluff]

Do you have a header row, or does the actual data start in row 1?

 

[Scott T]

Assuming the data is as posted if the sheets have headers then ranges need to be changed

Sub copytofile()
Dim lrlv As Long
Dim lrfile As Long
Dim ws As Worksheet
Dim wsf As Worksheet
Set ws = Sheets("London Vigs")
Set wsf = Sheets("FILE")
lrlv = ws.Cells(Rows.Count, 1).End(xlUp).Row
lrfile = wsf.Cells(Rows.Count, 1).End(xlUp).Row
wsf.Range("A1:A" & lrfile).ClearContents 'if you have headers then change A1 to where your data starts
lrfile = 0 ' if you have headers the remove this line
For x = 1 To lrlv
    pos = InStr(UCase(ws.Cells(x, 1)), "(DONE)")
    If pos > 0 Then
    ws.Cells(x, 1).Copy wsf.Cells(lrfile + 1, 1)
    lrfile = wsf.Cells(Rows.Count, 1).End(xlUp).Row
    End If
    
Next x
End Sub

 

[Fluff]

An alternative, based on no header row

Sub FilterCopy()

   With Sheets("London Vigs")
      .Rows(1).Insert
      .Range("A1:B1").AutoFilter 1, "*done*"
      .Range("A1").CurrentRegion.Offset(1).Copy Sheets("File").Range("A" & Rows.Count).End(xlUp).Offset(1)
      .Range("A1").AutoFilter
      .Rows(1).Delete
   End With
End Sub

 

[MikeWip]

Thanks so much for this. it does work well but I'd like to improve it if you are okay with that?

Instead of copy/pasting all data containing the wording (DONE) from the 1st tab to the 2nd one, can we do cut/paste? so the first tab stays clean and the 2nd one plays as an archive...

In my reports, I do have headers and sub-headers. I'd like to keep both.

the header is always showing up.
sub-headers should also be showing up on the 2nd tab (FILE) if the data underneath each sub-header has the wording (DONE)

example:

HEADER: CARS
sub header: MERCEDES
A class
E Class (DONE)
S Class (DONE)
CLK Class

sub header: BMW
M3 (DONE)
M5
M6 (DONE)
X5

sub header: Jaguar
F type (DONE)
X type


I'd like to get a button on the first tab to cut/paste all data with (DONE) into the 2nd tab, INCLUDING its relevant sub header, like BMW...
the sub headers should only be copy/pasted so they also stay within the first tab.

Thanks a lot and sorry for adding more.
I do appreciate your help.

thanks Scott

 

[Fluff]

Do you always have a blank row between the sub headings?

 

[MikeWip]

Do you always have a blank row between the sub headings?

No, there isn't a blank row between the sub-headings...

so it goes like:

Sub-heading
data
data (DONE)
data
Sub-heading
data (DONE)
data
sub-heading
data (DONE)
data


etc.

hope that helps.

cheers Fluff

 

[Fluff]

hope that helps.

Nope
Will the sub headings always be underlined? If not how do we detect the sub headings?

 

[Scott T]

Are the sub-headings underlined font or the cell has an bottom border? We need a way to identify the sub-header from the data.

 

[MikeWip]

Are the sub-headings underlined font or the cell has an bottom border? We need a way to identify the sub-header from the data.

Thanks guys.

All sub heading start with a Co:xxxxx

Like Co: Rolex
Co: Longines

Does that help somehow?