Essentially the macro should take the user input, search all sheets in the relevant excel file and if there's a match copy a value to a bookmark.
However the macro runs without copying over the data. If the UserForm2.Textbox1.Text line is replaced with the input, e.g. "January" - i.e. skipping the UserForm entry step - it works fine.
Code:
Sub Macro()
Dim xlApp As New Excel.Application
Dim xlWb As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim Result As String
Set xlWb = xlApp.Workbooks.Open("File Name.xlsx")
xlWb.Application.Visible = False
xlWb.Application.WindowState = xlMinimized
For Each xlSheet In xlWb.Sheets
If xlSheet.Range("B3").Value = UserForm2.TextBox1.Text Then
Result = xlSheet.Range("B9").Value
FillBM "bm3", Result
Exit For
End If
Next xlSheet
xlWb.Close
xlApp.Quit
End Sub
Any ideas how to fix this?
Cheers