Switch between unknown wbk name grab a value and go back

I know this is going to easy, but for the life of me...
I would like to be able to go thru all open workbooks and switch to one
whose name changes all the time but has the same beginning,
GetReadySheet_*.xls.
I found this code

Sub ListBooks()
Dim wbk As Workbook
For Each wbk In Workbooks
MsgBox wbk.Name
Next wbk
End Sub

but I don’t know enough to be able to use the
Windows(“GetReadySheet_*.xls”).Activate to go to the GetReadySheet workbook,
grab a cell value out of E6 and then go back to the file with the code. Any
thoughts…

Sub ListBooks()
Dim wbk As Workbook
Dim sValue As String
For Each wbk In Workbooks
If Left(wbk.Name, 13) = "GetReadySheet" Then
sValue = wbk.Worksheets("WorkSheetName").Range("E1").Value
End If
Next wbk
End Sub

Dim wbk As Workbook
dim i as integer

do
i=i+1
loop until lefT(wbk.Name,len("GetReadySheet")="GetReadySheet"

myval=wkb.worksheets("Sheet1").range("E6")

or some such

hope this helps

Originally Posted by Dale

I know this is going to easy, but for the life of me...
I would like to be able to go thru all open workbooks and switch to one
whose name changes all the time but has the same beginning,
GetReadySheet_*.xls.
I found this code

Sub ListBooks()
Dim wbk As Workbook
For Each wbk In Workbooks
MsgBox wbk.Name
Next wbk
End Sub

but I don’t know enough to be able to use the
Windows(“GetReadySheet_*.xls”).Activate to go to the GetReadySheet workbook,
grab a cell value out of E6 and then go back to the file with the code. Any
thoughts…

Works great but I did not notice before the worksheet does not have a
standard name either. What would I do fo ("WorkSheetName") ?

"J.Evans.1970@gmail.com" wrote:

> Sub ListBooks()
> Dim wbk As Workbook
> Dim sValue As String
> For Each wbk In Workbooks
> If Left(wbk.Name, 13) = "GetReadySheet" Then
> sValue = wbk.Worksheets("WorkSheetName").Range("E1").Value
> End If
> Next wbk
> End Sub
>
>

If you only have one worksheet in that "getreadysheet*.xls" workbook, then you
could use:

sValue = wbk.Worksheets(1).Range("E1").Value

If you have more than one worksheet, how do you know which one to use?

Dale wrote:
>
> Works great but I did not notice before the worksheet does not have a
> standard name either. What would I do fo ("WorkSheetName") ?
>
> "J.Evans.1970@gmail.com" wrote:
>
> > Sub ListBooks()
> > Dim wbk As Workbook
> > Dim sValue As String
> > For Each wbk In Workbooks
> > If Left(wbk.Name, 13) = "GetReadySheet" Then
> > sValue = wbk.Worksheets("WorkSheetName").Range("E1").Value
> > End If
> > Next wbk
> > End Sub
> >
> >

--

Dave Peterson