Cut and pastimg rows with vba

[Johnreg]

Hi all,

First of apologies I know this topic has been discussed a lot but any example I have tried to use doesnt work.

So in column A has a job type and column E has if its completed.

So for example job type would electrical, plumbing and engineering.

So what I need to happen is if a row contains and of the above job types and completed in column E the row cuts to a different workbook.



As you might I have guessed I am very new to VBA and any help / starting point would be much appreciated.

Thanks

 

[rpaulson]

try this.
make a new sheet and name it Completed

Sub Move_Compelted()

Dim rs As Worksheet
Set rs = Worksheets("Completed")

For r = 2 To Range("A" & Rows.Count).End(xlUp).Row

If (Cells(r, "A") = "electrical" Or Cells(r, "A") = "plumbing" Or Cells(r, "A") = "engneering") And Cells(r, "E") <> "" Then

wr = rs.Range("A" & Rows.Count).End(xlUp).Row + 1
Rows(r).Copy Destination:=rs.Range("A" & wr)
Rows(r).ClearContents
End If

Next r
End Sub

 

[Johnreg]

Hi,

Thanks so much for response.

Probably should have said the each row will have to got into a different sheet on different workbook.

So basically I have workbook called finished and sheets called plumbing electrical engineering. So depending on the job type it will have to go into a different sheet.

Sorry if this make it much more complicated.

 

[rpaulson]

Sub Move_Compelted()

Dim rs As Worksheet

For r = 2 To Range("A" & Rows.Count).End(xlUp).Row

If Cells(r, "A") = "electrical" And Cells(r, "E") <> "" Then Set rs = Worksheets("electrical")
If Cells(r, "A") = "plumbing" And Cells(r, "E") <> "" Then Set rs = Worksheets("plumbing")
If Cells(r, "A") = "engneering" And Cells(r, "E") <> "" Then Set rs = Worksheets("engneering")

wr = rs.Range("A" & Rows.Count).End(xlUp).Row + 1
Rows(r).Copy Destination:=rs.Range("A" & wr)
Rows(r).ClearContents
End If

Next r
End Sub

 

[rpaulson]

the code above wont work

Sub Move_Compelted()

Dim rs As Worksheet
For r = 2 To Range("A" & Rows.Count).End(xlUp).Row

If Cells(r, "A") = "electrical" And Cells(r, "E") <> "" Then
    Set rs = Worksheets("electrical")
    GoSub doit
End If

If Cells(r, "A") = "plumbing" And Cells(r, "E") <> "" Then
    Set rs = Worksheets("plumbing")
    GoSub doit
End If
  
If Cells(r, "A") = "engneering" And Cells(r, "E") <> "" Then
    Set rs = Worksheets("engneering")
    GoSub doit
End If

Next r

Exit Sub

doit:
wr = rs.Range("A" & Rows.Count).End(xlUp).Row + 1
Rows(r).Copy Destination:=rs.Range("A" & wr)
Rows(r).ClearContents
Return

End Sub

 

[Johnreg]

Hi,

Thanks that worked perfectly much appreciated.

Just wondering could you explain this part of the code "For r = 2".

 

[rpaulson]

it create a loop. it starts at row 2 and loop thru all rows you have in column "A"

Loop in Excel VBA

Looping is one of the most powerful programming techniques. A loop in Excel VBA enables you to loop through a range of cells with just a few codes lines.

www.excel-easy.com

 

[Johnreg]

Thanks for the explanation and the link

 

[Johnreg]

Hi,

Just one more question what is the wr at bottom of the code .

 

[rpaulson]

the wr finds the next blank row at the bottom of the destinatino sheet to write the data to.