Solved

VB Excel Error 9 when returning from copying info from 2 other workbooks

Posted on 2011-03-17
12
375 Views
Last Modified: 2012-05-11
And frustration sets in!!!!!!!!  Hello Experts!

Yesterday I asked a question on this same topic and thought I had this fixed.  However something is still stopping my VB from completing correctly.  

I have a Login sheet that the clerk will select their name and shift.  Then when they select an object called "Machine Pull" it fires a second macro to open a Form for "Machine Pull" and then opens 2 other workbooks that it copies data from back into the Machine Money Pull w/b

When It gets to the bottom of the code it keeps erroring out with Error 9.

I am attaching the code again.  First the calling VB and then the VB that is running when it errors.  The effected code area is highlighted with ****This is where it errors*****.
Sub LoginOpenMachinePullForm()
    Sheets("Login").Select
    Sheets("Machine Pull").Visible = True
    Application.Run "'Machine Money Pull.xlsm'!MachinePullInput"
End Sub

**********************
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
            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\BACKOFFICE\Desktop\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
      Workbooks("Machine Money Pull.xlsm").Activate
 ' ********* THIS IS WHERE IT ERRORS*******
        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

Open in new window

0
Comment
Question by:wlwebb
  • 6
  • 6
12 Comments
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 500 total points
ID: 35162800
You are using inferred object references which can get you into trouble. Always use specific object references to avoid this potential problem.

' insert a code to Make Machine pull the active worksheet
       With Workbooks("Machine Money Pull.xlsm")
           .Activate
           .Sheets("Machine Pull").Select
           .Sheets("DataLastShift4MachPull").Visible = False
       End With

Also notice that I removed all selects and use of the "GoTo" method as you should never have to make selections from VBA.

Kevin
0
 

Author Comment

by:wlwebb
ID: 35162824
Kevin
Thanks for the reply,  But it still errors out.  This time it makes it to the next line .Sheets("Machine Pull").Select.

Now the error is Run-time error 1004. Select method of worsheet class failed.

????????
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 500 total points
ID: 35162883
Let's get rid of all the select and activate statements. They really don't do anything useful anyway.

Replace:

' insert a code to Make Machine pull the active worksheet
       With Workbooks("Machine Money Pull.xlsm")
           .Activate
           .Sheets("Machine Pull").Select
           .Sheets("DataLastShift4MachPull").Visible = False
       End With

With:

' insert a code to Make Machine pull the active worksheet
       Workbooks("Machine Money Pull.xlsm").Sheets("DataLastShift4MachPull").Visible = False

Kevin
0
 

Author Comment

by:wlwebb
ID: 35162902
Ok that works but then errors to the next line with the same error.  So what I did was comment out the
 '        Range("AA21").Select
'        Range("Z23").Select
        Application.Goto Reference:="EmployeeSelector"


However, It kicks me back to the "Login" w/s.  I need to stop on the "Machine Pull" worksheet for the clerk to input on.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35162911
Is the worksheet "Machine Pull" visible?

Kevin
0
 

Author Comment

by:wlwebb
ID: 35162945
Yes momentarily, but then it goes back to the Login w/s
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35162958
OK, it's hard for me to know what to do because I don't have all the information.

To solve this problem you need to think about what workbook has what worksheets and the make all references fully qualified. For example, if you want to do something with cell A1 on sheet Sheet1 in workbook xyz.xls you need to fully qualify the object:

   Workbooks("xyz.xls").Worksheets("Sheet1").Range("A1").Value = 123

Anything less will lead you into trouble.

Kevin
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 500 total points
ID: 35162962
And get rid of all GoTo calls and selects. They don't do anything useful. If you want to bring a workbook to the foreground:

   Workbooks("xyz.xls").Activate

If you want to activate a worksheet:

   Workbooks("xyz.xls").Worksheets("Sheet1").Activate

Do not select any ranges or worksheets just to do something to them. You can manipulate worksheet properties including setting cell values without the worksheet or workbook being active.

Kevin
0
 

Author Comment

by:wlwebb
ID: 35162999
You lost me there.

If you are referring to the lines of code Range("AA21") and Range("Z23") and Application.Goto Reference:="EmployeeSelector" then this may clarify what I was "Attempting".  Those ranges and goto's are all related to the w/s "Machine Pull".  The form is wide so the input area I want them to start on is off screen and selecting those ranges allowed the cell I wanted showing to be on the screen.  There most likely is a better way to do it, but I'm a newbie.

The program opens the W/B on a w/s called "Login".  That sheet simply let's the clerk identify who they are and what the date is.

Then on that "Login" form they select an object that (and I don't know if I will use the right term here but in plain terms it...) either fires a macro to:
A) copy data from two other workbooks back to hidden temp data sheets and puts them onto the "Machine Pull" input form..... OR.....
B) Go to a different input form

the firing of A) is the one that isn't working here as it isn't putting the clerk back onto the "Machine Pull" form
0
 

Author Comment

by:wlwebb
ID: 35163017
Ok, then as an example of replacing a Goto.  

Let's use this example.  Let's say I have a named range called "EmployeeSelector"

Workbook = xyz.xlsm
Worksheet = Machine Pull
Range = cell a4

to replace that code what would I replace it with.  To get this far I did a record macro so that I could get that code.  What is the cleaner VB say to refer to that.
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 35163036
>Let's use this example.  Let's say I have a named range called "EmployeeSelector"

   Workbooks("xyz.xlsm").Range("EmployeeSelector").Value = 123

>The form is wide so the input area I want them to start on is off screen and selecting those ranges allowed the cell I wanted showing to be on the screen.

To scroll the active window to a specific cell (that cell is located in the top left corner of the window) use the ActiveWindow.ScrollColumn and ActiveWindow.ScrollRow properties as illustrated in the example code below.

   ActiveWindow.ScrollRow = 20
   ActiveWindow.ScrollRow = ActiveCell.Row
   ActiveWindow.ScrollColumn = 10
   ActiveWindow.ScrollColumn = ActiveCell.Column
   ActiveWindow.ScrollColumn = Range("X10").Column

Note that since the ScrollRow and ScrollColumn belong to the ActiveWindow property, there is no way to scroll a worksheet unless it is active.

   Workbooks("xyz.xls").Activate
   Workbooks("xyz.xls").Worksheets("Sheet1").Activate

Kevin
0
 

Author Comment

by:wlwebb
ID: 35163062
Ok, I understand the scrolling issue.  But, I don't understand:
>Let's use this example.  Let's say I have a named range called "EmployeeSelector"
   Workbooks("xyz.xlsm").Range("EmployeeSelector").Value = 123

That value (123) is variable.  The named range "EmployeeSelector" is tied to a "Data Validation List" that only allows the clerk to select a name from the list.  Yes I would like to pass that name from the "Login" sheet (which is also tied to a "Data Validation List") where they selected their name originally before they got to this "Machine Pull" sheet, but I haven't gotten that far yet.


0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

759 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

20 Experts available now in Live!

Get 1:1 Help Now