Find Cell and Copy adjacent value to another location
 

I am using the following code to search through multiple sheets for a value.
I need to select the data in the column to the left of the value and copy it
to another sheet. What do I need to replace the "C.value = "" " statement
with to select and copy to another location?

Sub FindEID()
Dim c As Range
Dim sFirstHit As String 'address of first hit

For i = 1 To Sheets.Count 'look in all sheets
With Range("c:c") 'use column c on all sheets
Set c = .Find(Range("EIDNO").Value, LookIn:=xlValues) 'find EIDNo from
Report Worksheet
If Not c Is Nothing Then 'find value
sFirstHit = c.Address
Do
'c.Value = ""

Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With
Next i
End Sub

Find Cell and Copy adjacent value to another location
 

Try this code...

Public Sub FindEID()
Dim rngFound As Range
Dim rngToSearch As Range
Dim rngcopyto As Range
Dim wksCurrent As Worksheet
Dim wksCopyTo As Worksheet
Dim strFirstAddress As String

Set wksCopyTo = Sheets("Sheet1")
Set rngcopyto = wksCopyTo.Range("A1")

For Each wksCurrent In Worksheets
Set rngToSearch = wksCurrent.Range("C1").EntireColumn
Set rngFound = rngToSearch.Find(Range("EIDNO").Value,
LookIn:=xlValues)
If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Do
rngFound.EntireRow.Copy rngcopyto
Set rngcopyto = rngcopyto.Offset(1, 0)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop While rngFound.Address < strFirstAddress
End If

Next wksCurrent

End Sub

In answer to your specific question you can just copy your range object "c",
to a destination range (rngCopyTo in my code). There was one other problem in
your code. You would have hit an infinite loop as "c" would potentailly not
go to nothing. You had the address string as your stopper, but you did not
use it...

HTH

"JJalomo" wrote:

I am using the following code to search through multiple sheets for a value.
I need to select the data in the column to the left of the value and copy it
to another sheet. What do I need to replace the "C.value = "" " statement
with to select and copy to another location?

Sub FindEID()
Dim c As Range
Dim sFirstHit As String 'address of first hit

For i = 1 To Sheets.Count 'look in all sheets
With Range("c:c") 'use column c on all sheets
Set c = .Find(Range("EIDNO").Value, LookIn:=xlValues) 'find EIDNo from
Report Worksheet
If Not c Is Nothing Then 'find value
sFirstHit = c.Address
Do
'c.Value = ""

Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With
Next i
End Sub

Find Cell and Copy adjacent value to another location
 

Sorry this code copies the entire row. You can just modify this to

rngCurrent.offset(0,1).copy rngCopyTo

HTH

"Jim Thomlinson" wrote:

Try this code...

Public Sub FindEID()
Dim rngFound As Range
Dim rngToSearch As Range
Dim rngcopyto As Range
Dim wksCurrent As Worksheet
Dim wksCopyTo As Worksheet
Dim strFirstAddress As String

Set wksCopyTo = Sheets("Sheet1")
Set rngcopyto = wksCopyTo.Range("A1")

For Each wksCurrent In Worksheets
Set rngToSearch = wksCurrent.Range("C1").EntireColumn
Set rngFound = rngToSearch.Find(Range("EIDNO").Value,
LookIn:=xlValues)
If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Do
rngFound.EntireRow.Copy rngcopyto
Set rngcopyto = rngcopyto.Offset(1, 0)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop While rngFound.Address < strFirstAddress
End If

Next wksCurrent

End Sub

In answer to your specific question you can just copy your range object "c",
to a destination range (rngCopyTo in my code). There was one other problem in
your code. You would have hit an infinite loop as "c" would potentailly not
go to nothing. You had the address string as your stopper, but you did not
use it...

HTH

"JJalomo" wrote:

I am using the following code to search through multiple sheets for a value.
I need to select the data in the column to the left of the value and copy it
to another sheet. What do I need to replace the "C.value = "" " statement
with to select and copy to another location?

Sub FindEID()
Dim c As Range
Dim sFirstHit As String 'address of first hit

For i = 1 To Sheets.Count 'look in all sheets
With Range("c:c") 'use column c on all sheets
Set c = .Find(Range("EIDNO").Value, LookIn:=xlValues) 'find EIDNo from
Report Worksheet
If Not c Is Nothing Then 'find value
sFirstHit = c.Address
Do
'c.Value = ""

Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With
Next i
End Sub