Trying to get macro to select/activate an open workbook with an unknown name

[Teladianium]

Hi Guys,

Quick disclaimer, Im not great at the code and am learning by plagiarism. so I have the sub "ListWorkbooks" which works great at providing a list of all the open wb. i am looking for a "buzzword" in the wb names. In this case, I am looking for "STAN", the rest of the name is made up with unknown variable info. What I want to do is assign the workbook name to the STAN variable in the "assignWB" sub so I can select or activate that wb to pull data and dump in in another wb (which will be another buzz worded open wb).

The issue I get is in the opening wb line "Windows(STANWB).Select" i also tryed Workbooks(STANWB).Select and .Activate. i even tried Application.Workbooks which it didn't like either. The msgbox retuns the corect wb name complete with the file ext. any help really appriciated.

Sub ListWorkbooks()

Sheets("Open workbooks").Range("A1").Select
Cells.Select
Selection.ClearContents
Range("A1").Select
Dim Rng As Range
Dim WorkRng As Range
Dim WTWB
On Error Resume Next
Set WorkRng = ActiveWorkbook.Sheets("Open workbooks").Range("A1")
xNum1 = Application.Workbooks.Count
For i = 1 To xNum1
xNum2 = Application.Workbooks(i).Sheets.Count
WorkRng.Offset(i - 1, 0).Value = Application.Workbooks(i).Name
If Application.Workbooks(i).Find(what:="STAN") = True Then WTWB = Application.Workbooks(i).Name

For j = 1 To xNum2
WorkRng.Offset(i - 1, j).Value = Application.Workbooks(i).Sheets(j).Name
Next
Next

End Sub


Sub assignWB()

Dim STANWB
ActiveWorkbook.Sheets("Open workbooks").Select
Range("A:A").Find(what:="STAN").Select
STANWB = ActiveCell.Value
MsgBox STANWB
Windows(STANWB).Select
MsgBox STANWB

End Sub


Below just an exaple of the returned data

Report_builder.xlsx
	A	B	C	D	E	F
1	book1.xlsm	Sheet 1	Sheet 2	Sheet 3	Sheet 4
2	wbrandom_476_STAN_complete_roandom.xlsm	Info	Sheet 2	Sheet 3	Sheet 4
3	some_other_WB.xlsm	Sheet 1	Sheet 2	Sheet 3	Sheet 4
4	And_another.xlsm	Sheet 1	Sheet 2	Sheet 3	Sheet 4
5
6
Sheet1

 

[HaHoBe]

Hi Teladianium,

what about

Sub assignWB()
Dim wb As Workbook
Dim wbStan As Workbook

For Each wb In Workbooks
'  If UCase(wb.Name) Like "*STAN*.*" Then
  If InStr(1, wb.Name, "STAN") > 0 Then
    Set wbStan = wb
    Exit For
  End If
Next wb

If Not wbStan Is Nothing Then
  With wbStan
    MsgBox .FullName & vbCrLf & vbCrLf & .Path & vbCrLf & vbCrLf & .Name & _
       vbCrLf & vbCrLf & .Worksheets.Count, , "Infos for wbStan"
    .Activate
  End With
End If

Set wbStan = Nothing
End Sub

Ciao,
Holger

 

[Teladianium]

Hi Holger,

Thanks for your reply. I have tried your suggestion, and it does locate the wb and selects it. I lack the knowledge to repetitively select the wb.

my macro ships data from tables in three different wb's and fills in a form. then returns to the original wb offsets a row and does it again etc. would I need to run this one each time I need to refer back to that wb? or can I after running it use the wbStan somehow to re-select? I tried "Windows(wbStan).select / activate" and copied you "with" and tried (just to test) but I cant get it to work

Sub re_select()
With wbStan
MsgBox .FullName & vbCrLf & vbCrLf & .Path & vbCrLf & vbCrLf & .Name & _
vbCrLf & vbCrLf & .Worksheets.Count, , "Infos for wbStan"
.Activate
End With
End Sub

Again thanks a lot

 

[johnnyL]

@Teladianium See if the following helps you:

Shortened @HaHoBe code

Sub assignWB()
'
    Dim wb      As Workbook
    Dim wbStan  As Workbook
'
' Locate workbook with 'STAN' somewhere in the name
    For Each wb In Workbooks
        If InStr(1, wb.Name, "STAN") > 0 Then
            Set wbStan = wb
'
            Exit For
        End If
    Next wb
'
    If wbStan Is Nothing Then
        MsgBox "Workbook name containing 'STAN' was not found."
        Exit Sub
    End If
'
' At this point wbStan can be used as needed
'
End Sub

 

[HaHoBe]

Hi Teladianium,

please use code-tags for displaying code here in the future.

Sample copies from wbStan to three different sheets and uses a loop, you would need to adapt the names of workbooks as well as sheets and the ranges to suit

Sub assignWB()
Dim wb As Workbook
Dim wbStan As Workbook

Dim wsFirst As Worksheet
Dim wsSecond As Worksheet
Dim wsThird As Worksheet

Dim lngCnt As Long

Set wsFirst = Workbooks("First WB.xlsm").Worksheets("First Sheet")
Set wsSecond = Workbooks("Second WB.xlsm").Worksheets("Second Sheet")
Set wsThird = Workbooks("Third WB.xlsm").Worksheets("Third Sheet")

For Each wb In Workbooks
'  If UCase(wb.Name) Like "*STAN*.*" Then
  If InStr(1, wb.Name, "STAN") > 0 Then
    Set wbStan = wb
    Exit For
  End If
Next wb

If Not wbStan Is Nothing Then
  With wbStan
    With .Worksheets("Sample Data")
      For lngCnt = 10 To 50
        .Cells(lngCnt, "A").Resize(1, 4).Copy wsFirst.Cells(lngCnt + 5, "D")
        .Cells(lngCnt, "E").Resize(1, 3).Copy wsSecond.Cells(lngCnt + 10, "F")
        .Cells(lngCnt, "H").Resize(1, 7).Copy wsThird.Cells(lngCnt + 15, "X")
      Next lngCnt
    End With
  End With
End If

Set wbStan = Nothing
Set wsThird = Nothing
Set wsSecond = Nothing
Set wsFirst = Nothing
End Sub

Holger

 

[Teladianium]

Hi Holger,
Sorry, I've not replied. I had a change of plan at work and had to travel. i am back online now and will get back into this in a day or so. Thanks