CODEWITHSUNDEEP
vbaexcel
bdkong
bdkong

Reputation: 191

Excel macro only works when certain workbook is opened first

I have a macro for Excel that transfers data between three workbooks. Two of the workbooks stay the same and are merely templates. The third workbook is an output from a business management system. It changes, but will always start with "RFQ_". For instance; RFQ_14787, RFQ_14839, RFQ_63528.

The code below was written to cycle through the open workbooks, select the one that starts with "RFQ_", and store that name in a variable to be used throughout the code.

In testing this code I have discovered that it only works if the workbook entitled "RFQ_XXXXX" is opened first.

All credit for the code goes to @Tim Williams and his answer to one of my other questions here.

Sub Tester2()

Dim wbName As String, shtSrc As Worksheet, shtDest As Worksheet

wbName = GetRfqWbName("RFQ_")
If Len(wbName) = 0 Then
    MsgBox "Didn't find the RFQ workbook!"
    Exit Sub
Else
    'for example: you can substitute the sheet names instead
    Set shtSrc = Workbooks(wbName).Sheets(1)
    Set shtDest = Workbooks("Transfer Template.xlsm").Sheets(1)
End If

shtSrc.Range("J51").Copy shtDest.Range("B1")

End Sub
'get the name of the first workbook which begins with sName...
Function GetRfqWbName(sName As String) As String

Dim wb As Workbook

For Each wb In Workbooks
    If wb.Name Like sName & "*" Then GetRfqWbName = wb.Name
    Exit For
Next wb
End Function

I'm only looking for an explanation, out of curiosity, but if anyone has a way to run the macro without opening the "RFQ_" workbook first I would appreciate it.

Upvotes: 1

Views: 1806

Answers (1)

Wooz
Wooz

Reputation: 306

Unfortunally I am not allowed to comment so I have to make this an answer but as fas as I see the problem is in:

For Each wb In Workbooks
    If wb.Name Like sName & "*" Then GetRfqWbName = wb.Name
    Exit For
Next wb

The Exit For is not part of the if-clause so it will break the loop everytime after the first Workbook has been checked - whether successfully or not...

Edit: Change the code as follows to solve the problem

For Each wb In Workbooks
    If wb.Name Like sName & "*" Then 
       GetRfqWbName = wb.Name
       Exit For
    End If
Next wb

Upvotes: 2

Related Questions