Adding a new line to an open workbook, Getting Object doesn't support this property or method error

[Nlhicks]

Sub EnterNewLine()
    
'Declare object variables
    
    Dim wbMaster As Workbook
    Dim wb As Workbook
    Dim masterNextRow As Long
    Dim wsLinesMaster As Worksheet
    Dim wbUpdate As Workbook
    Dim wsFacility As Worksheet
    Dim strFile As String
    Dim strWbVersion As String
    Dim StorRng As Variant
    Dim SearchCell As Integer
   

  '//// adjust the path to match, this is my sample for testing \\\'
    Const cstrPath As String = "C:\Users\nhicks\Documents\Ratings\Saved Versions\"
    Const cstrStFileName As String = "WAPA-UGPR Facility Rating and SOL Record (Data File)_v"
    Const cstrwbMaster As String = "WAPA-UGPR Facility Rating and SOL Record (Master).xlsm"
    Const cstrMasterUpdate As String = "Line Update"
    Const cstrShFacility As String = "Facility Ratings & SOLs (Lines)"

  '/// will find any xls, xlsb, xlsx or xlsm workbook that start with cstrStFileName
  '/// and should deliver the highest number from there
  strWbVersion = HighestVersion(cstrPath, ".xlsm", cstrStFileName)
  If Len(strWbVersion) = 0 Then
    MsgBox "Could not spot a version of " & vbCrLf & cstrStFileName & _
        vbCrLf & "in Path " & cstrPath, vbInformation, cstrMsgTitle
    GoTo end_here
  End If

  For Each wb In Workbooks
    If LCase(wb.Name) = LCase(cstrwbMaster) Then
      Set wbMaster = wb
      Exit For
    End If
  Next wb
  If wbMaster Is Nothing Then
    If Dir(cstrwbMaster) <> "" Then
      Set wbMaster = Workbooks.Open(cstrwbMaster)
    Else
      MsgBox "Could not find '" & cstrwbMaster & "' in current folder. Please open workbook and start again.", vbInformation, cstrMsgTitle
      GoTo end_here
    End If
  End If
  If Evaluate("ISREF('[" & cstrwbMaster & "]" & cstrMasterUpdate & "'!A1)") Then
    Set wsLinesMaster = wbMaster.Sheets(cstrMasterUpdate)
  Else
    MsgBox "Sheet '" & cstrMasterUpdate & "' not found in workbook '" & cstrwbMaster, vbInformation, cstrMsgTitle
    GoTo end_here
  End If
  
  For Each wb In Workbooks
    If LCase(wb.Name) = LCase(strWbVersion) Then
      Set wbUpdate = wb
      Exit For
    End If
  Next wb
  If wbUpdate Is Nothing Then
    If Dir(IIf(Right(cstrPath, 1) = "\", cstrPath, cstrPath & "\") & strWbVersion) <> "" Then
      Set wbUpdate = Workbooks.Open(IIf(Right(cstrPath, 1) = "\", cstrPath, cstrPath & "\") & strWbVersion)
    Else
      MsgBox "Could not find '" & strWbVersion & "' in " & cstrPath & ". Please open workbook and start again.", vbInformation, cstrMsgTitle
      GoTo end_here
    End If
  End If
  If Evaluate("ISREF('[" & strWbVersion & "]" & cstrShFacility & "'!A1)") Then
    Set wsFacility = wbUpdate.Sheets(cstrShFacility)
  Else
    MsgBox "Sheet '" & cstrShFacility & "' not found in workbook '" & strWbVersion, vbInformation, cstrMsgTitle
    GoTo end_here
  End If

  Application.ScreenUpdating = False

 

  
    masterNextRow = wbUpdate.wsFacility.Range("B" & wbUpdate.wsFacility.Rows.Count).End(xlUp).Offset(1).Row
    
    wbUpdate.wsFacility.Cells(masterNextRow, 2).Value = wbMaster.wsLinesMaster.Range("A45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 3).Value = wbMaster.wsLinesMaster.Range("B45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 4).Value = wbMaster.wsLinesMaster.Range("C45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 5).Value = wbMaster.wsLinesMaster.Range("D45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 6).Value = wbMaster.wsLinesMaster.Range("E45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 7).Value = wbMaster.wsLinesMaster.Range("F45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 8).Value = wbMaster.wsLinesMastere.Range("G45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 9).Value = wbMaster.wsLinesMaster.Range("H45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 10).Value = wbMaster.wsLinesMaster.Range("I45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 11).Value = wbMaster.wsLinesMaster.Range("J45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 12).Value = wbMaster.wsLinesMaster.Range("K45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 13).Value = wbMaster.wsLinesMaster.Range("L45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 14).Value = wbMaster.wsLinesMaster.Range("M45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 15).Value = wbMaster.wsLinesMaster.Range("N45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 16).Value = wbMaster.wsLinesMaster.Range("O45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 17).Value = wbMaster.wsLinesMaster.Range("P45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 18).Value = wbMaster.wsLinesMaster.Range("Q45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 19).Value = wbMaster.wsLinesMaster.Range("R45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 20).Value = wbMaster.wsLinesMaster.Range("S45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 21).Value = wbMaster.wsLinesMaster.Range("T45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 22).Value = wbMaster.wsLinesMaster.Range("U45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 23).Value = wbMaster.wsLinesMaster.Range("V45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 24).Value = wbMaster.wsLinesMaster.Range("W45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 25).Value = wbMaster.wsLinesMaster.Range("Z45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 26).Value = wbMaster.wsLinesMaster.Range("Y45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 27).Value = wbMaster.wsLinesMaster.Range("Z45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 28).Value = wbMaster.wsLinesMaster.Range("AA45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 29).Value = wbMaster.wsLinesMaster.Range("AB45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 30).Value = wbMaster.wsLinesMaster.Range("AC45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 31).Value = wbMaster.wsLinesMaster.Range("AD45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 32).Value = wbMaster.wsLinesMaster.Range("AE45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 33).Value = wbMaster.wsLinesMaster.Range("AF45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 34).Value = wbMaster.wsLinesMaster.Range("AG45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 35).Value = wbMaster.wsLinesMaster.Range("AH45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 36).Value = wbMaster.wsLinesMaster.Range("AI45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 37).Value = wbMaster.wsLinesMaster.Range("AJ45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 38).Value = wbMaster.wsLinesMaster.Range("AK45").Value
    wbUpdate.wsFacility.Cells(masterNextRow, 39).Value = wbMaster.wsLinesMaster.Range("AL45").Value
   


end_here:
  Set wsLinesMaster = Nothing
  Set wsFacility = Nothing
  Set wbUpdate = Nothing
  Set wbMaster = Nothing
  Application.ScreenUpdating = True
  Exit Sub

End Sub

 

[Nlhicks]

Error is occurring on this line:

   masterNextRow = wbUpdate.wsFacility.Range("B" & wbUpdate.wsFacility.Rows.Count).End(xlUp).Offset(1).Row

 

[Domenic]

You'll need to remove your workbook reference. Your variable wsFacility refers to your worksheet, so that's all you need. So your line should be...

masterNextRow = wsFacility.Range("B" & wsFacility.Rows.Count).End(xlUp).Offset(1).Row

And the same thing for your code that follows.

Hope this helps!

 

[Nlhicks]

Perfect Domenic that worked for that error but now it is giving me code execution has been interrupted on this line:

 wsFacility.Cells(masterNextRow, 4).Value = wsLinesMaster.Range("C45").Value

Here is my locals window if that helps

 

[Nlhicks]

Thanks, It did copy the data into the wb

 

[Domenic]

It looks like all your variables have been defined correctly, and the syntax seems fine. So I don't see why you would get that error message. Is that the exact message that you received? Does the same thing happen when you run it again?

 

[Domenic]

By the way, that section of your code with all those lines can be re-written as follows...

    With wsFacility
        masterNextRow = .Range("B" & .Rows.Count).End(xlUp).Offset(1).Row
    End With
    
    With wsLinesMaster.Range("A45:AL45")
        wsFacility.Cells(masterNextRow, 2).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

 

[Nlhicks]

It appears to be interrupted after every line, if I push continue all the way through the code it finishes the code but the user is not going to want to keep pushing continue. Any suggestions?

 

[Nlhicks]

The way you had me rewrite it only generates one interruption and as long as I say continue it finishes the program. Thanks

 

[Domenic]

See if the solution offered in the following link helps . . .

Excel VBA App stops spontaneously with message "Code execution has been halted"

From what I can see on the web, this is a fairly common complaint, but answers seem to be rarer. The problem is this: We have a number of Excel VBA apps which work perfectly on a number of users'

stackoverflow.com

Does it?