Clever Geek Handbook

VBA. How do I set a book type variable when I only know the partial book name

I currently have several books open. One of them is sometimes called "trade1", sometimes "trade2" or whatever, but "trade" always starts. I am trying to set a variable so that regardless of the name, if it is open and starts with "trade", this book will now be a variable. So in the whole macro, I can reference this book and use it for things like Trade.activate to select this book. Here is my current code where I am getting runtime error "9": index error below range:

Dim Trade as Workbook

Set Trade = workbooks("trade" & "*" & ".xls")

I know it can be done with a function, but hoped it could be done easier. Is this possible, or am I on a stupid errand?

+3


source to share


1 answer


you can loop through open books and find the correct one and then set a variable:



Dim Trade As Workbook
Dim t As Workbook

For Each t In Workbooks
    If Left(t.Name, 5) = "trade" Then
        Set trade = Workbooks(t.Name)
    End If
Next t
+3


source






More articles:


All Articles