Refering to cells in different workbooks

[GrahamAdshead]

I am trying to write a macro to :
1. search a series of workbooks for a particular value on a particular worksheet (called "Rework") using a For-Next loop.
2. take the value of an adjecent cell and place it in a particular cell in a "master" workbook.
I can find and open each workbook, but how do I refer to the "Rework" worksheet in that workbook and then search the cells?
Sorry if this is very obvious, but I'm a beginner at this.

 

[PHV]

A starting point:
Workbooks("workbook name").Worksheets("Rework").Cells.Find ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GrahamAdshead]

Thanks for your help. Can you tell me why this code doesn't work?

If Nextbook.Worksheets("Rework").Cells(x, y) = WkNo Then
ThisWorkbook.Worksheets("Sheet1").Cells(PrRow, 7) = WkNo
End If

It calls up "Error 424 Object required", but I thought that "NextBook.Worksheets" were the objects.

Thanks.

 

[PHV]

What is Nextbook ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GrahamAdshead]

Hi.
NextBook is a variable that contains the full path to a file that I want to search.
e.g. NextBook = "C:\\........\MyFile.xls"

I want to search a series of files for a number (WkNo) and then put the data from an adjecent cell into another file.
NextBook's value is found by using the following code:

DirName = (CurDir & "\")
Nextbook = Dir(DirName & "*.xls")

It is looped by using: Do While NextBook <>"" loop

 

[GlennUK]

Show the rest of your code. ( well, the rest of that loop anyway )

Nextbook should be a string. Are you opening the workbook that is specified in Nextbook string? If so, then the workbook object would be Workbooks(NextBook), as in Workbooks(NextBook).Worksheets("Rework").Cells(x, y)

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.