Solved

Excel 2007 VB Run-time Error 9 when running a module

Posted on 2011-03-16
10
475 Views
Last Modified: 2012-05-11
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)




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

*****************************************************************

Open in new window

0
Comment
Question by:wlwebb
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
If you are trying to activate that workbook, you need this:
Workbooks("Machine Money Pull").Activate

Open in new window

0
 

Author Comment

by:wlwebb
Comment Utility
StephenJR
Attempted that.  Still same error.
0
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
Is that workbook definitely open and is the name correct?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
Try

Workbooks("Machine Money Pull.xls").Activate

Also try with the full path name.
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
Comment Utility
The essence would seem too be replace:

set workbooks("Book2") = activeworkbook
with
workbooks("book2").Activate

I don't believe your syntax is correct!


Chris
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
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
 
0
 

Author Closing Comment

by:wlwebb
Comment Utility
This did the trick!  Thanks for the quick help.
0
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
Wow that's a bit harsh. That's exactly what I said in the first post!
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
>>> 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
0
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
Chris - just to be clear, I am not begrudging you the points.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now