Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
call procedure problem
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 -- stan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
call procedure problem
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) On Mon, 17 Aug 2009 07:35:01 -0700, stan wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
call procedure problem
thank you!
-- stan "Chip Pearson" wrote: 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) On Mon, 17 Aug 2009 07:35:01 -0700, stan wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual basic 6.5 question using combo box and call procedure. | Excel Discussion (Misc queries) | |||
The remote procedure call failed. (Exception from HRESULT: 0x80070 | Excel Worksheet Functions | |||
Run-time Error '5' - Invalid procedure call or argument | Excel Discussion (Misc queries) | |||
to call procedure in a worksheet in a module | Excel Discussion (Misc queries) | |||
Run-time error '5': Invalid Procedure Call or Argument | Excel Discussion (Misc queries) |