moving rows to another sheet

[rbacon100]

Hi all

I need help.

I have alot of sheets to work it and cant do all with conditional formatting because it will take much time that I dont have.

What I need is this.

I have sheet with 2 columns of data (a and b columns)

A column is codes and b column are links.

What I need is this.

Find specific text in column B and move that whole row(actually only a and b column) to sheet named Black. So if some word is on b10, macro should move that A10 and B10 to "Black" sheet

Now I am thinkin that macro code or something to look at specific words in for example column c or d or macro code to ask what words should he find.

I need to be able to write different words somewhere, then run macro and that macro moves rows to Black sheet.

For example, word i need to find in column b are "ADS"
Now macro should move all rows to Black that have ADS in column B.

But I have more words to look at so I need to be able to just write all words somewhere and that macro code do rest.
Also, when macro moving all that, I dont want to leave empty space.


Thanks in advance

 

[Claus Busch]

Hi,

Am Thu, 15 May 2014 14:01:35 -0700 (PDT) schrieb (e-mail address removed):

Find specific text in column B and move that whole row(actually only a and b column) to sheet named Black. So if some word is on b10, macro should move that A10 and B10 to "Black" sheet

Now I am thinkin that macro code or something to look at specific words in for example column c or d or macro code to ask what words should he find.

try:

Sub Test()
Dim myStr As String
Dim LRow As Long

myStr = Application.InputBox("Write the expected string", _
"String Search", Type:=2)

If myStr = "" Or myStr = "False" Then Exit Sub

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("A1:B" & LRow).AutoFilter field:=2, _
Criteria1:="=*" & myStr & "*"
.Range("A2:B" & LRow).Copy _
Sheets("Black").Cells(Rows.Count, 1).End(xlUp)(2)
.AutoFilterMode = False
End With
End Sub


Regards
Claus B.

 

[rbacon100]

Hi,



Am Thu, 15 May 2014 14:01:35 -0700 (PDT) schrieb (e-mail address removed):






try:



Sub Test()

Dim myStr As String

Dim LRow As Long



myStr = Application.InputBox("Write the expected string", _

"String Search", Type:=2)



If myStr = "" Or myStr = "False" Then Exit Sub



With ActiveSheet

LRow = .Cells(Rows.Count, 1).End(xlUp).Row

.Range("A1:B" & LRow).AutoFilter field:=2, _

Criteria1:="=*" & myStr & "*"

.Range("A2:B" & LRow).Copy _

Sheets("Black").Cells(Rows.Count, 1).End(xlUp)(2)

.AutoFilterMode = False

End With

End Sub





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Thanks Claus.

Also Can I add after that copining to Black. Rest to copy to another sheet?

Thank you so much

 

[Claus Busch]

Hi,

Am Fri, 16 May 2014 01:23:42 -0700 (PDT) schrieb (e-mail address removed):

Also Can I add after that copining to Black. Rest to copy to another sheet?

the following code copies the strings with the expected substring to
"Black" and the rest to "White". Modify the sheet names:

Sub Test()
Dim myStr As String
Dim LRow As Long

myStr = Application.InputBox("Write the expected string", _
"String Search", Type:=2)

If myStr = "" Or myStr = "False" Then Exit Sub

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("A1:B" & LRow).AutoFilter Field:=2, _
Criteria1:="=*" & myStr & "*"
.Range("A2:B" & LRow).Copy _
Sheets("Black").Cells(Rows.Count, 1).End(xlUp)(2)
.Range("A1:B" & LRow).AutoFilter Field:=2, _
Criteria1:="<>*" & myStr & "*"
'Modify the sheet name
.Range("A2:B" & LRow).Copy _
Sheets("White").Cells(Rows.Count, 1).End(xlUp)(2)
.AutoFilterMode = False
End With
End Sub


Regards
Claus B.