wlwebb
asked on
Excel 2007 VB Run-time Error 9 when running a module
Hello experts. I'm back with a mental stumbling block.
Have an Excel project that I have created a user Login sheet that when the clerk clicks on an object it runs a module to open a second and third worksheets and copy some data back into the first workbook.
Problem is when it gets to the line to go back to the original workbook (in this case wb "Machine Money Pull") and go to needed worksheet (ws "Machine Pull") I am getting a Run-Time Error '9' Out of Range.
Below is the code. I have highlighted where it errors out with a string of ? marks to easily identify (I think)
Have an Excel project that I have created a user Login sheet that when the clerk clicks on an object it runs a module to open a second and third worksheets and copy some data back into the first workbook.
Problem is when it gets to the line to go back to the original workbook (in this case wb "Machine Money Pull") and go to needed worksheet (ws "Machine Pull") I am getting a Run-Time Error '9' Out of Range.
Below is the code. I have highlighted where it errors out with a string of ? marks to easily identify (I think)
Sub LoginOpenMachinePullForm()
Sheets("Login").Select
Sheets("Machine Pull").Visible = True
Application.Run "'Machine Money Pull.xlsm'!MachinePullInput"
End Sub
*******************************
MODULE8
Sub MachinePullInput()
' Retrieve The Last Shift's LVL Totals for Day as of the End of that Shift to copy as values to the ws DataLastShift4MachPull
'
Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
Dim ws1_1 As Worksheet
Dim ws2_1 As Worksheet
Dim ws3_1 As Worksheet
Dim strCopyFromFileInMachineMoneyPull As String, strSaveToFileInMachineMoneyPull As String
Dim inputRange_ws1_1 As Range
Dim outputRange_ws2_1 As Range
Dim LastRowInput_ws1_1 As Long
' Application.Visible = False
Dim intResponse As Integer
intResponse = MsgBox("Are you sure you want to begin a New Machine Money Pull?", vbOKCancel + vbInformation)
If intResponse = vbOK Then
'Resume, the user pressed ok
' This defaults the Calendar object to today
Application.Goto Reference:="MachinePullTopofForm"
Sheets("Machine Pull").Calendar1.Value = Date
'add back Application.ScreenUpdating = False
Sheets("Machine Pull").Select
Sheets("DataLastShift4MachPull").Visible = True
' End of defaults for Calendar object to today
'~~> wb1 in this instance is the "Machine Money Pull" workbook
Set wb1 = ActiveWorkbook
Set ws1_1 = wb1.Sheets("DataLastShift4MachPull")
'~~> Change file path here so I can use a file path workbook to update where files are instead of going to VB code
'~~> wb3 in this instance is the "FilePaths" workbook which contains the paths
Set wb3 = Workbooks.Open("C:\Documents and Settings\Bill.BILL_BACKOFFICE\Desktop\BCJ Master Forms\FilePaths.xlsx")
Set ws3_1 = wb3.Sheets("FilePaths")
FilePath = ws3_1.Range("E3").Value '<~~ Contains value like C:\Temp\
strCopyFromFileInMachineMoneyPull = FilePath & "Shift Details Data.xlsx"
strSaveToFileInMachineMoneyPull = "Machine Money Pull.xlsm"
wb3.Close savechanges:=False
'~~> End Change file path settings
'~~> Test if save to workbook is open
On Error Resume Next
'~~> This will set wb2 name
'~~> wb2 in this instance is "Shifts Data Details" workbook
Set wb2 = Workbooks(Dir(strCopyFromFileInMachineMoneyPull))
Set ws2_1 = wb2.Sheets("DataLastShift4MachPullTemp")
On Error GoTo 0
If wb2 Is Nothing Then
Set wb2 = Workbooks.Open(strCopyFromFileInMachineMoneyPull)
Set ws2_1 = wb2.Sheets("DataLastShift4MachPullTemp")
End If
'~~> Begin Save info
Set wb2 = ActiveWorkbook
LastRowInput_ws2_1 = ws2_1.Range("A" & Rows.Count).End(xlUp).Row
LastRowOutput_ws1_1 = ws1_1.Range("A" & Rows.Count).End(xlUp).Row
Set inputRange_ws2_1 = ws2_1.Range("A5:AC" & LastRowInput_ws2_1)
Set outputRange_ws1_1 = ws1_1.Range("A" & LastRowOutput_ws1_1)
inputRange_ws2_1.Copy
outputRange_ws1_1.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
' Save updated data file then close
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
' insert a code to Make Machine pull the active worksheet
*??????????THE FOLLOWING IS WHERE IT HANGS AND GIVES ME A RUN-TIME ERROR '9' Subscript out of range ??????????*
Set Workbooks("Machine Money Pull") = ActiveWorkbook
Sheets("Machine Pull").Select
Application.Goto Reference:="MachinePullTopofForm"
Sheets("DataLastShift4MachPull").Visible = False
Range("AA21").Select
Range("Z23").Select
Application.Goto Reference:="EmployeeSelector"
Application.ScreenUpdating = True
Else
'The user pressed cancel
'Cancel event
End If
End Sub
**************************************************************
FOLLOWING STILL PART OF MODULE8 (But it doesn't look like it uses this one)
Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
*****************************************************************
ASKER
StephenJR
Attempted that. Still same error.
Attempted that. Still same error.
Is that workbook definitely open and is the name correct?
Try
Workbooks("Machine Money Pull.xls").Activate
Also try with the full path name.
Workbooks("Machine Money Pull.xls").Activate
Also try with the full path name.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry forgot to re-instaye your nsame after a quick test!
The essence would seem too be replace:
set workbooks("Machine Money Pull") = activeworkbook
with
workbooks("Machine Money Pull").Activate
I don't believe your syntax is correct!
Chris
The essence would seem too be replace:
set workbooks("Machine Money Pull") = activeworkbook
with
workbooks("Machine Money Pull").Activate
I don't believe your syntax is correct!
Chris
ASKER
This did the trick! Thanks for the quick help.
Wow that's a bit harsh. That's exactly what I said in the first post!
>>> That's exactly what I said in the first post!
I agree in principle ... what I did was to indicate what was wrong and then how to fix it.
Since it did not work for the asker it seemed to me there was an issue with understanding of the one line entry and that was what I sought to resolve ... successfuly it would seem to me.
Chris
I agree in principle ... what I did was to indicate what was wrong and then how to fix it.
Since it did not work for the asker it seemed to me there was an issue with understanding of the one line entry and that was what I sought to resolve ... successfuly it would seem to me.
Chris
Chris - just to be clear, I am not begrudging you the points.
Open in new window