call duplicated
 

New to VBA

I'm using the following code which is based on a range:

Sub update_finance()
'

If Range("b4").Value = "expenses1" Then Workbooks.Open
"C:\expenses1.xlsm"
Windows("dollars.xlsm").Activate
Sheets("Estimate").Select
Application.Run "'dollars.xlsm'!SAVE_expenses1"

If Range("b4").Value = "expenses2" Then Workbooks.Open "C:\expenses2.xlsm"
Windows("dollars.xlsm").Activate
Sheets("Estimate").Select
Application.Run "'dollars.xlsm'!SAVE_expenses2"
End Sub
--
"expenses1" runs OK but when I use "expenses2" in the cell it flips back to
the first one. I will have six range variables when I'm done but I didn't
see much sense in moving on until the first two are resolved.

Not sure how to remedy: if-then-else?


stan

call duplicated
 

Probably because you are using the same sheet name in both workbooks, but
are not qualifying it when you refer to it with both workbooks open. You
should, either close the first workbook if you are finished with it before
opening the second one. Or you can use syntax like:

Workbooks("Expenses1").Sheets("Estimate").Select
Workbooks("Expensex2.")Sheets("Estimate").Select

That way, VBA knows which workbook you want to select Sheets("Extimate") in.


"stan" wrote in message
...
New to VBA

I'm using the following code which is based on a range:

Sub update_finance()
'

If Range("b4").Value = "expenses1" Then Workbooks.Open
"C:\expenses1.xlsm"
Windows("dollars.xlsm").Activate
Sheets("Estimate").Select
Application.Run "'dollars.xlsm'!SAVE_expenses1"

If Range("b4").Value = "expenses2" Then Workbooks.Open
"C:\expenses2.xlsm"
Windows("dollars.xlsm").Activate
Sheets("Estimate").Select
Application.Run "'dollars.xlsm'!SAVE_expenses2"
End Sub
--
"expenses1" runs OK but when I use "expenses2" in the cell it flips back
to
the first one. I will have six range variables when I'm done but I didn't
see much sense in moving on until the first two are resolved.

Not sure how to remedy: if-then-else?


stan

call duplicated
 

hi
i think your structure is wrong. as is it is more top down than dicision
making.
try this ...
Sub update_finance2()

If Range("b4").Value = "expenses1" Then
Workbooks.Open "C:\expenses1.xlsm"
Windows("dollars.xlsm").Activate
Sheets("Estimate").Select
Application.Run "'dollars.xlsm'!SAVE_expenses1"
Else
If Range("b4").Value = "expenses2" Then
Workbooks.Open "C:\expenses2.xlsm"
Windows("dollars.xlsm").Activate
Sheets("Estimate").Select
Application.Run "'dollars.xlsm'!SAVE_expenses2"
End If
End If
End Sub

i don't have your data or macros so i only tested the decisions. they work
with the above structure.
regards
FSt1

"stan" wrote:

New to VBA

I'm using the following code which is based on a range:

Sub update_finance()
'

If Range("b4").Value = "expenses1" Then Workbooks.Open
"C:\expenses1.xlsm"
Windows("dollars.xlsm").Activate
Sheets("Estimate").Select
Application.Run "'dollars.xlsm'!SAVE_expenses1"

If Range("b4").Value = "expenses2" Then Workbooks.Open "C:\expenses2.xlsm"
Windows("dollars.xlsm").Activate
Sheets("Estimate").Select
Application.Run "'dollars.xlsm'!SAVE_expenses2"
End Sub
--
"expenses1" runs OK but when I use "expenses2" in the cell it flips back to
the first one. I will have six range variables when I'm done but I didn't
see much sense in moving on until the first two are resolved.

Not sure how to remedy: if-then-else?


stan