Using FIND to copy and paste....

[chefdt]

I want to copy a cell from an active sheet to a summary sheet using FIND. Just not hitting it yet.

I found some code from DeBruin that may get me started.

Once I copy the cell in the active sheet I want to scan a SUMMARY sheet where my value from the active sheet will be in Column A. Once it hits a match, I want to paste the value to a specific Column in that row where FIND made the match.




DT

 

[mumps]

Where is the cell you want to copy in the active sheet? How do you decide which cell to copy?

 

[chefdt]

It will always be in J1

Summary sheet should have the activesheet name somewhere in Column A.

 

[mumps]

Try this macro:

Sub CopyCell()
    Application.ScreenUpdating = False
    Dim foundVal As Range
    Set foundVal = Sheets("Sheet2").Range("A:A").Find(Sheets("Sheet1").Range("J5"), LookIn:=xlValues, lookat:=xlWhole)
    If Not foundVal Is Nothing Then
        Sheets("Sheet2").Cells(foundVal.Row, "B") = Sheets("Sheet1").Range("J5")
    Else
        MsgBox ("Value not found.")
    End If
    Application.ScreenUpdating = True
End Sub

You didn't specify into which column of Sheet2 you wanted to paste. The code pastes to column B. If you want to paste to a different column, change the "B" in this line:

Sheets("Sheet2").Cells(foundVal.Row, "B") = Sheets("Sheet1").Range("J5")

to whichever column you want to paste.

 

[chefdt]

Ultimately I will have this looping through all sheets and will need to change "Sheet1" to activesheet.

Would it be - find(sheets.activesheet.range("J5"), LookIn:=xlValues ?

Try this macro:

Sub CopyCell()
    Application.ScreenUpdating = False
    Dim foundVal As Range
    Set foundVal = Sheets("Sheet2").Range("A:A").Find(Sheets("Sheet1").Range("J5"), LookIn:=xlValues, lookat:=xlWhole)
    If Not foundVal Is Nothing Then
        Sheets("Sheet2").Cells(foundVal.Row, "B") = Sheets("Sheet1").Range("J5")
    Else
        MsgBox ("Value not found.")
    End If
    Application.ScreenUpdating = True
End Sub

You didn't specify into which column of Sheet2 you wanted to paste. The code pastes to column B. If you want to paste to a different column, change the "B" in this line:

Sheets("Sheet2").Cells(foundVal.Row, "B") = Sheets("Sheet1").Range("J5")

to whichever column you want to paste.

 

[mumps]

Will cell J5 in all the sheets ever have duplicate values?