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 dont 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€¦

Switch between unknown wbk name grab a value and go back
 

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

Switch between unknown wbk name grab a value and go back
 

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
Dale Wrote:
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 dont 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€¦


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=533933

Switch between unknown wbk name grab a value and go back
 

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

" 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

Switch between unknown wbk name grab a value and go back
 

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") ?

" 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