Move rows to another worksheet with vba

[cwm7102]

I need help to find a value or name in a column and move all row
containing that value to another worksheet. It will be good if there'
an input box to key in the value to search. In my excel file i
"MoveRows" in the Open worksheet the value or rather the name to b
search is in column D titled Person. I would like to find all rows wit
say John in Col D and move it to the next blank row ie row7 in Close
worksheet

+-------------------------------------------------------------------
|Filename: MoveRows.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=316
+-------------------------------------------------------------------

 

[Gord Dibben]

Revised macro for your particular workbook "MoveRows"

Sub Copy_With_AutoFilter1()
'Ron de Bruin......Feb. 4th, 2007
'revised by Gord Dibben Apr. 2nd, 2012
Dim ws As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim str As String
Dim targrng As Range

Set ws = Sheets("Open")
Set rng = ws.Range("B5:IV5000") '<<<adjust to suit

str = InputBox("enter a search term")

ws.AutoFilterMode = False

'starting at column B the Field is 3....Person
rng.AutoFilter Field:=3, Criteria1:=str

Set WSNew = Sheets("Closed")
Set targrng = WSNew.Cells(Rows.Count, 2).End(xlUp) _
.Offset(1, 0)
ws.AutoFilter.Range.Copy
With targrng
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

ws.AutoFilterMode = False
End Sub



Gord

 

[merjet]

Gord's code works fine. You may want to change one line to save
typing.

str = Application.InputBox("enter a search term", Type:=8)

 

[cwm7102]

works great but i also want the rows moved to be deleted and no blan
rows in "Open" worksheet.

;1600352']Revised macro for your particular workbook "MoveRows"

Sub Copy_With_AutoFilter1()
'Ron de Bruin......Feb. 4th, 2007
'revised by Gord Dibben Apr. 2nd, 2012
Dim ws As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim str As String
Dim targrng As Range

Set ws = Sheets("Open")
Set rng = ws.Range("B5:IV5000") '<<<adjust to suit

str = InputBox("enter a search term")

ws.AutoFilterMode = False

'starting at column B the Field is 3....Person
rng.AutoFilter Field:=3, Criteria1:=str

Set WSNew = Sheets("Closed")
Set targrng = WSNew.Cells(Rows.Count, 2).End(xlUp) _
.Offset(1, 0)
ws.AutoFilter.Range.Copy
With targrng
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

ws.AutoFilterMode = False
End Sub



Gord



On Mon, 2 Apr 2012 08:55:30 +0000, cwm7102

I need help to find a value or name in a column and move all rows
containing that value to another worksheet. It will be good if there's
an input box to key in the value to search. In my excel file in
"MoveRows" in the Open worksheet the value or rather the name to be
search is in column D titled Person. I would like to find all row with
say John in Col D and move it to the next blank row ie row7 in Closed
worksheet.


+-------------------------------------------------------------------+
|Filename: MoveRows.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=316|
+-------------------------------------------------------------------+

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[merjet]

Sub MoveRows()
Dim iEndF As Integer
Dim iEndT As Integer
Dim iRow As Integer
Dim c As Range
Dim str1 As String
str1 = Application.InputBox("enter a search term", Type:=8)
iEndF = Sheets("Open").Cells(4, 4).End(xlDown).Row
iEndT = 1 + Sheets("Closed").Cells(4, 4).End(xlDown).Row
For iRow = iEndF To 5 Step -1
If Sheets("Open").Cells(iRow, 4) = str1 Then
Sheets("Open").Rows(iRow).Copy
Sheets("Closed").Cells(iEndT, 1).PasteSpecial (xlAll)
Sheets("Open").Cells(iRow, 4).EntireRow.Delete
iEndT = iEndT + 1
End If
Next iRow
End Sub