Retrieve "values" from other workbooks

[Axcell]

I pieced together the following basic routine to retrieve cell value
from other workbooks in various folders. I am able to search fo
specific excel workbooks and get the data I need but I am trying t
obtain the cell's values and not the Cell's formulas.

This is what I have so far, I'd appreciate it if someone could help m
find my way...Thanks much!

Sub TSSearch()
Dim Timebook As Workbook
Dim TSbook As Workbook
Dim SourceRange As Range
Dim TargetRange As Range
Dim rnum As Long
Dim i As Long
Dim a As Long

Sheets("Time Sheet").Select
Columns("A:B").Select
Selection.ClearContents

jNumb = InputBox("Enter Job Number")

Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Timesheets"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
.Filename = "*" & jNumb & ".xls"
If .Execute() > 0 Then
Set Timebook = ThisWorkbook
rnum = 1
For i = 1 To .FoundFiles.Count
Set TSbook = Workbooks.Open(.FoundFiles(i), , ,
"test")
Set SourceRange = TSbook.Worksheets("Tim
Sheet").Range("R19:S21")
a = SourceRange.Rows.Count
Set TargetRange = Timebook.Worksheets("Tim
Sheet").Cells(rnum, 1)
SourceRange.Copy TargetRange
TSbook.Close
rnum = rnum + a
Sheets("Time Sheet").Cells(i, 3) = .FoundFiles(i)
Next i
End If
End With
Application.ScreenUpdating = True
End Su

 

[Dave Peterson]

It sounds like your routine is working ok except for returning the formulas
instead of the values.

You could replace this line

SourceRange.Copy TargetRange

with

TargetRange.Resize(SourceRange.Rows.Count, SourceRange.Columns.Count).Value _
= SourceRange.Value

====
Alternatively, you could have done a Copy|pastespecial|values followed by
Copy|pastespecial|formats (if you needed the formats, too.)

 

[Axcell]

Didn't see your reply under my original post. That did the trick, thank
Dave