Solved: Set cell value from one workshee equal to found cell in another workbook

[kathyb0527]

I have code that copies data by columns from one workbook to another, but now I need to add information to a few individual cells via find (I think). I've made several attempts over the past few days, but I just can't seem to get it. I've tried For statements and If statements, but they didn't return the cell value (rgFound). Now I've gotten the value I want, I can't get it to transfer to the destination workbook. :banghead:
Here is my latest attempt.
WB As Workbook, Dest As Workbook, Rng As Range, Tgt As Range, Rws As Long
Dim c As Range, cel As Range
Dim stStudy As String, stLLOQ As String
Dim stSourcefilename As String
Dim rgFound As Range, rgCalInfo As Range
Set rgFound = WB.Sheets(2).Range("A1:A100").Find("Regression", lookat:=xlPart, LookIn:=xlValues)
Dest.Range("B6") = rgFound.Value
If someone could tell me what I'm doing wrong, I think I could work it out. Also, I'm not sure this is the most efficient way to do this since I have to do it ~9 more times with different finds.

Thanks for your time.

Kathyb0527

[Mavyak]

The Dest object is a workbook so it doesn't have a "Range" object. You need to specify the sheet in workbook Dest where you want to past the found value. You were missing a "Dim" keyword on the first line of your code so I assume it's been copied/pasted hastily and then a good meaty portion of the code got cut out to simplify your example. Try the code below and if you need help tweaking or understanding it, holler back.
Sub myMacro()
Dim Dest As Workbook
Dim w As Worksheet
Dim rgFound As Range

Set Dest = Workbooks("<Enter the Destination Workbook's Name Here>") '<--This is the workbook where the found value will be copied
Set w = ThisWorkbook.Sheets(2) '<--This is the sheet that will be searched for the value "Regression"

'This will find the cell with "Regression" in it and return that cell.
'If the value you really want is actually adjacent to the cell you will need
'to add a ".Offset(<# of rows>, <# of columns over>)" to the end after the
'last closing parentheses
Set rgFound = w.Range("A1:A100").Find("Regression", LookIn:=xlValues, LookAt:=xlPart)

If rgFound Is Nothing Then
MsgBox "Regression not found"
Else
Dest.Worksheets("<Enter the sheet name from the Destination workbook here>").Range("B6") = rgFound.Value
End If

Set rgFound = Nothing
Set w = Nothing
Set Dest = Nothing
End Sub

[kathyb0527]

Perfect. I always have a hard time with Workbook, Workbooks, Worksheet and Sheets etc. I think it's an "object with block"!

Thanks,
Kathyb

[mdmackillop]

Hi Kathyb,
Remember to mark your threads solved.
Regards
MD