Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2011-03-17
12
381 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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 

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
 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

828 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