I want to activate another active (open) workbook only if its name is the same as what I have recorded in a cell in my workbook. If it does return the same name then it is activated, otherwise a message box displays.
Example:
In my Reports workbook on a worksheet name “formula” in cell B33, I have the value “JulyData”. This represents the name of another workbook (i.e. JulyData.xls)
Before a routine is run to import data into Reports.xls I want to verify that the name of the source workbook is actually “JulyData.xls” (as verified to B33 on my “formula” worksheet) and not “AugData.xls”. If it is “JulyData.xls” then I want the procedure to make it the active workbook and start importing data from selected cells. If it is not, then a message box will display.
I know this code doesn’t work:
TW =
Worksheets("formula").Range("B33").Value
If Windows.Activate = TW Then
Windows.Activate
MsgBox ("Error - Data workbook name
does not match." & vbCr & _
"Select correct month for data
import."), vbOKOnly, "Data", a, a
I need some help please
Activate a workbook based on a value in a cell in another workbook
soconne
Wayne Pfeffer
Dim destinationWB As Workbook
Dim sourceWB As Workbook
Dim cellValue
Set destinationWB = ThisWorkbook 'Reports WB
Set sourceWB = Workbooks("julydata") 'July data WB
cellValue=sourceWB.Worksheets("Sheet1").Range("a1").Value
destinationWB.Worksheets("sheet1").Range("a2").Value=cellValue
Tadwick
Sub ActivateWB()
Dim wbname As Range
Dim wbfound As Boolean
Dim wb As Workbook
Set wbname = ThisWorkbook.Sheets("formula").Range("B33")
wbfound = False
For Each wb In Application.Workbooks
If wb.Name = wbname.Text & ".xls" Then
wbfound = True
wb.Activate
Exit For
End If
Next
If Not wbfound Then MsgBox "WB not found"
End Sub