Run Time Errors: 91; 2147417848; 1004

shyannh5

New Member
Joined
Apr 5, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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:

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


'
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

jsb1921

Board Regular
Joined
Aug 14, 2020
Messages
70
Office Version
  1. 2007
Platform
  1. Windows
  2. Web
Replace
VBA Code:
    closedBook.Sheets("Summary").Activate
    closedBook.Sheets("Summary").Select
    closedBook.Sheets("Summary").Range("A1").Select
with
VBA Code:
    closedBook.Sheets("Summary").Select
    Range("A1").Select
what other lines are having problem
VBA Code:
    ThisWorkbook.Sheets("V2").Select
    shiftDate = Range("B3")
is same as
shiftDate = Sheets("V2").cell(3,"B").value
 
Last edited:
Solution

shyannh5

New Member
Joined
Apr 5, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Replace
VBA Code:
    closedBook.Sheets("Summary").Activate
    closedBook.Sheets("Summary").Select
    closedBook.Sheets("Summary").Range("A1").Select
with
VBA Code:
    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

Board Regular
Joined
Aug 14, 2020
Messages
70
Office Version
  1. 2007
Platform
  1. Windows
  2. Web
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.
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,052
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

You can also use
VBA Code:
Application.Goto closedBook.Sheets("Summary").Range("A1")
rather than
VBA Code:
    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

New Member
Joined
Apr 5, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 5, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You can also use
VBA Code:
Application.Goto closedBook.Sheets("Summary").Range("A1")
rather than
VBA Code:
    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

Board Regular
Joined
Aug 14, 2020
Messages
70
Office Version
  1. 2007
Platform
  1. Windows
  2. Web
After closedBook.Close SaveChanges:=True
add
set closedBook = nothing
 

shyannh5

New Member
Joined
Apr 5, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,793
Messages
5,638,359
Members
417,023
Latest member
Zimbo38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top