Hi,

I have a spreadsheet with 4 sheets (Sheet 1,2,3 and
Total). I have written this code which will look at Sheet
1 and autofilter by the name I have entered in my Input
Box.

Dim Name1 As String
Name1 = InputBox("Please Enter Name: ", "name1")
Sheets("Sheet1").Select
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=Name1
End Sub

What I want is code that when I can put a name in my Input
Box. It will search Sheet1, Sheet2, Sheet3 and copy any
rows that have that name in to the totals sheet.

Here is some code for find and delete duplicates, so you can modify i
so rather than delete it copies and pastes.....have a go see what yo
think!

Simon

Heres the code

Sub RemoveDuplicateCells()
Dim Myrange As Range
Dim C As Range
Dim DelRange As Range
Dim FindRange
Set Myrange = Intersect(ActiveSheet.UsedRange, Columns("D"))
If Myrange Is Nothing Then Exit Sub
Application.ScreenUpdating = False
FindRange = Array("D", "D", "D")
For Each elem In FindRange
Set C = Myrange.Find(elem, Myrange.Cells(1), xlValues, xlPart)
If Not C Is Nothing Then
If DelRange Is Nothing Then Set DelRange = Rows(C.Row)
firstaddress = C.Address
Do
Set C = Myrange.FindNext(C)
Set DelRange = Union(DelRange, Rows(C.Row))
Loop While firstaddress < C.Address
End If
Next
Application.ScreenUpdating = True
If DelRange Is Nothing Then Exit Sub
DelRange.Delete shift:=xlUp
End Sub


Public Sub Delete_In_H()
Const MyColumn As Integer = 4
Dim lngRow As Integer
Dim maxRow As Integer

maxRow = Cells(ActiveSheet.Rows.Count, MyColumn).End(xlUp).Row

For lngRow = maxRow To 1 Step -1
If Cells(lngRow, MyColumn).Value = "D" Then

Cells(lngRow, 3).Delete
Cells(lngRow, 4).Delete
Cells(lngRow, 5).Delete
Cells(lngRow, 6).Delete
Cells(lngRow, 7).Delete

End If
Next lngRow
End Su

--
Message posted from http://www.ExcelForum.com

Simon,

I think this'll work great for the problem I'm having(there's a new post
above this thread about it). But, how would I go about copying just one
column from everywhere that it finds the argument?

IOW, I'm looking for an "X" in, say Column B. I want all values from Column
A that have an X in column B.

Thanks!
Shawn

"Simon Lloyd " wrote in message
...
Here is some code for find and delete duplicates, so you can modify it
so rather than delete it copies and pastes.....have a go see what you
think!

Simon

Heres the code

Sub RemoveDuplicateCells()
Dim Myrange As Range
Dim C As Range
Dim DelRange As Range
Dim FindRange
Set Myrange = Intersect(ActiveSheet.UsedRange, Columns("D"))
If Myrange Is Nothing Then Exit Sub
Application.ScreenUpdating = False
FindRange = Array("D", "D", "D")
For Each elem In FindRange
Set C = Myrange.Find(elem, Myrange.Cells(1), xlValues, xlPart)
If Not C Is Nothing Then
If DelRange Is Nothing Then Set DelRange = Rows(C.Row)
firstaddress = C.Address
Do
Set C = Myrange.FindNext(C)
Set DelRange = Union(DelRange, Rows(C.Row))
Loop While firstaddress < C.Address
End If
Next
Application.ScreenUpdating = True
If DelRange Is Nothing Then Exit Sub
DelRange.Delete shift:=xlUp
End Sub


Public Sub Delete_In_H()
Const MyColumn As Integer = 4
Dim lngRow As Integer
Dim maxRow As Integer

maxRow = Cells(ActiveSheet.Rows.Count, MyColumn).End(xlUp).Row

For lngRow = maxRow To 1 Step -1
If Cells(lngRow, MyColumn).Value = "D" Then

Cells(lngRow, 3).Delete
Cells(lngRow, 4).Delete
Cells(lngRow, 5).Delete
Cells(lngRow, 6).Delete
Cells(lngRow, 7).Delete

End If
Next lngRow
End Sub


---
Message posted from http://www.ExcelForum.com/