call procedure problem

[stan]

New to VVA and trying to run a procedure that opens a particular workbook
then runs a procedure that pastes a

worksheet into the target worksheet from the source worksheet ("dollars").

There will be six tartet workbooks when I'm done but I can't get past the
first two.

It will open each workbook correctly if I exclude "dollars" procedures. If
I include them it only runs the first one

sucessfully.

What am I doing wrong?

Sub update_finance()
'

Application.DisplayAlerts = False
If Range("b4").Value = "expenses1" Then Workbooks.Open "C:\expenses1.xlsm"
Application.Run "'dollars1.xlsm'!SAVE_expenses1"

If Range("b4").Value = "expenses2" Then Workbooks.Open "C:\expenses2.xlsm"
Application.Run "'dollars2.xlsm'!SAVE_expenses2"
Application.DisplayAlerts=True

End Sub

 

[Chip Pearson]

My first guess is that in the first line

If Range("b4").Value = "expenses1" Then Workbooks.Open
"C:\expenses1.xlsm"

the Range("B4") is pointing to the ActiveSheet in the original
workbook. Then, the code opens Expenses.xlsm, and that workbook become
the Active Workbook, so in the second

If Range("b4").Value = "expenses2" Then Workbooks.Open
"C:\expenses2.xlsm"

the Range("B4") is pointing to the ActiveSheet in Expenses1.xlsm, not
B4 in the original workbook.

If you intend that B4 refer to the same cell in the original workbook,
set a Range reference to that cell and then use the value of that
reference in the code. E.g.,

Dim R As Range
Set R = Range("B4")
If R.Value = "expenses1" Then
Workbooks.Open "C:\Expenses1.xlsm"
Application.Run "'dollars1.xlsm'!SAVE_expenses1"
ElseIf R.Value = "expenses2" Then
Workbooks.Open "C:\Expenses2.xlsm"
Application.Run "'dollars2.xlsm'!SAVE_expenses2"
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[stan]

thank you!
--
stan

My first guess is that in the first line

If Range("b4").Value = "expenses1" Then Workbooks.Open
"C:\expenses1.xlsm"

the Range("B4") is pointing to the ActiveSheet in the original
workbook. Then, the code opens Expenses.xlsm, and that workbook become
the Active Workbook, so in the second

If Range("b4").Value = "expenses2" Then Workbooks.Open
"C:\expenses2.xlsm"

the Range("B4") is pointing to the ActiveSheet in Expenses1.xlsm, not
B4 in the original workbook.

If you intend that B4 refer to the same cell in the original workbook,
set a Range reference to that cell and then use the value of that
reference in the code. E.g.,

Dim R As Range
Set R = Range("B4")
If R.Value = "expenses1" Then
Workbooks.Open "C:\Expenses1.xlsm"
Application.Run "'dollars1.xlsm'!SAVE_expenses1"
ElseIf R.Value = "expenses2" Then
Workbooks.Open "C:\Expenses2.xlsm"
Application.Run "'dollars2.xlsm'!SAVE_expenses2"
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)