Run Time Errors: 91; 2147417848; 1004

[shyannh5]

Hi guys! I am having some issues with a line of code I wrote. I've researched the issues and come up with no solutions. A majority of the time, the code will run just fine; but sometimes, the code will initially return the error: runtime error 2147417848 - automation error the object invoked has disconnected from its clients. After debugging this error and rerunning the code, it pulls this error: runtime error 91 - object variable or with block variable not set. Occassionally it will also pull this error: runtime error 1004 select method of range class failed. The purpose of my code is to copy sheet "V2" in the current workbook I'm working in and then transfer the whole sheet plus sort its data into another closed workbook. The first two errors pull on the folowing line of code: ThisWorkbook.Sheets("V2").Copy Before:=closedBook.Sheets(5)

The third error pulls on this line of code: closedBook.Sheets("Summary").Range("A1").Select

I think I may have fixed the last error by adding the two lines found above it. If anyone could offer some advice, I would be extremely grateful! Here is my whole VBA code:

Option Explicit
'Const strSHEET_FORM_NAME As String = "Form"
'Const strSHEET_DRUMSUMMARY_NAME As String = "DrumSummary"
'Const strSHEET_SLURRYSUMMARY_NAME As String = "SlurrySummary"
'Const strSHEET_COATINGSUMMARY_NAME As String = "CoatingSummary"

Sub Transfer_Data()
'
' Transfer_Data Macro
'
Dim closedBook As Workbook
Dim RowCount As Long
Dim shiftDate As String
Dim shiftOption As String
Dim drumNo As Single
Dim slurryNo As Single
Dim ldNo As Single
Dim docNo As Single
Dim scrNo As Single
Dim ldtargetNo As Single
Dim doctargetNo As Single
Dim scrtargetNo As Single

Application.ScreenUpdating = False

    ThisWorkbook.Sheets("V2").Select
    shiftDate = Range("B3")
   
    ThisWorkbook.Sheets("V2").Select
    shiftOption = Range("B4")
   
    ThisWorkbook.Sheets("V2").Select
    drumNo = Range("A41")
   
    ThisWorkbook.Sheets("V2").Select
    slurryNo = Range("B37")
   
    ThisWorkbook.Sheets("V2").Select
     ldNo = Range("C23")
   
    ThisWorkbook.Sheets("V2").Select
     docNo = Range("E23")
   
    ThisWorkbook.Sheets("V2").Select
    scrNo = Range("G23")
   
    ThisWorkbook.Sheets("V2").Select
    ldtargetNo = Range("C24")
   
    ThisWorkbook.Sheets("V2").Select
    doctargetNo = Range("E24")
   
    ThisWorkbook.Sheets("V2").Select
    scrtargetNo = Range("G24")
   

    Set closedBook = Workbooks.Open("O:\SO_DATA\PSL Shift Report\ShiftReportData.xlsm")
    ThisWorkbook.Sheets("V2").Copy Before:=closedBook.Sheets(5)
      
   
    closedBook.Sheets("Summary").Activate
    closedBook.Sheets("Summary").Select
    closedBook.Sheets("Summary").Range("A1").Select
    RowCount = closedBook.Sheets("Summary").Range("A1").CurrentRegion.Rows.Count
        With closedBook.Sheets("Summary").Range("A1")
        .Offset(RowCount, 0) = shiftDate
        .Offset(RowCount, 1) = shiftOption
        .Offset(RowCount, 2) = drumNo
        .Offset(RowCount, 3) = slurryNo
        .Offset(RowCount, 4) = ldNo
        .Offset(RowCount, 5) = ldtargetNo
        .Offset(RowCount, 6) = docNo
        .Offset(RowCount, 7) = doctargetNo
        .Offset(RowCount, 8) = scrNo
        .Offset(RowCount, 9) = scrtargetNo
         End With
        
  ''drumNo refers to number of slurry heel containers
   
    closedBook.Close SaveChanges:=True

Application.ScreenUpdating = True
End Sub

'

 

[jsb1921]

Replace

    closedBook.Sheets("Summary").Activate
    closedBook.Sheets("Summary").Select
    closedBook.Sheets("Summary").Range("A1").Select

with

    closedBook.Sheets("Summary").Select
    Range("A1").Select

what other lines are having problem

    ThisWorkbook.Sheets("V2").Select
    shiftDate = Range("B3")
is same as
shiftDate = Sheets("V2").cell(3,"B").value

 

[shyannh5]

Replace

    closedBook.Sheets("Summary").Activate
    closedBook.Sheets("Summary").Select
    closedBook.Sheets("Summary").Range("A1").Select

with

    closedBook.Sheets("Summary").Select
    Range("A1").Select

what other lines are having problem

Thanks so much! The other line that I'm having issues with is ThisWorkbook.Sheets("V2").Copy Before:=closedBook.Sheets(5)

 

[jsb1921]

what error you are getting on the line ThisWorkbook.Sheets("V2").Copy Before:=closedBook.Sheets(5) now
at the beginning save the current workbook name and select that workbook before copying.
like mark said, why not use Before:=closedBook.Sheets(1)
it should not make any difference what order the sheets are in. atleast you will make progress.

 

[MARK858]

You can also use

Application.Goto closedBook.Sheets("Summary").Range("A1")

rather than

    closedBook.Sheets("Summary").Select
    Range("A1").Select

As well as the question @jsb1921 has asked and a silly question I know but does closedBook have at least 5 sheets when the code runs?

 

[shyannh5]

what error you are getting on the line ThisWorkbook.Sheets("V2").Copy Before:=closedBook.Sheets(5) now
at the beginning save the current workbook name and select that workbook before copying.
like mark said, why not use Before:=closedBook.Sheets(1)
it should not make any difference what order the sheets are in. atleast you will make progress.

It occasionally gives the error (I say occasionally since half the time I can run the code without any errors) Runtime Error 2147417848 - automation error the object invoked has disconnected from its clients. After debugging it and trying to re run it, it will give the error Runtime Error 91 - object variable or with block variable not set.

I place the sheet in the 5th tab due to the order I need it in the closedBook. I have tables, table of contents, and graphs in seperate tabs beforehand to allow other works ease of acces to visual information. This is how they requested it to be sorted and viewed, so the 5th place is for a reason.

 

[shyannh5]

You can also use

Application.Goto closedBook.Sheets("Summary").Range("A1")

rather than

    closedBook.Sheets("Summary").Select
    Range("A1").Select

As well as the question @jsb1921 has asked and a silly question I know but does closedBook have at least 5 sheets when the code runs?

Thanks! It does have 5 sheets. It always has a select four in front of it for reasons requested by other workers (table of contents, graphs, tables, etc.). And it has many sheets already placed in the file after the 5th sheet as well.

 

[jsb1921]

After closedBook.Close SaveChanges:=True
add
set closedBook = nothing

 

[shyannh5]

After closedBook.Close SaveChanges:=True
add
set closedBook = nothing

It is working so far with no errors! Thank you! I will update if there is another code error.