Run-time Error '1004': Application-defined or object-defined error.

whoosh

New Member
Joined
May 16, 2018
Messages
17
Dear Gurus,

The above-mentioned error keeps popping up when I execute the code below. It was running perfectly fine till I switched the sourceWB workbook and made changes to the code to reflect the change.

Since the change, i could not get the macro to run and it is driving me crazy.
Hope someone can help to highlight what could be the possible cause of this error.


Thanks in advance.


Code:
Option Explicit

Sub CopyExtRes_FRBO()
'
' Copy from FRBO
'
Dim sourceWB As Workbook, targetWB As Workbook, ws1 As Worksheet, i As Integer
Dim nRow As Long, lRow As Long, sht As String, sheet As Worksheet


Application.ScreenUpdating = False


Set sourceWB = Workbooks("FRBO (Private Apt).xls")
Set targetWB = Workbooks("ck's database_Residential (updating 01 May 2018).xlsm")
Set ws1 = sourceWB.Sheets("Sheet1")
lRow = ws1.Cells(Rows.Count, "B").End(xlUp).Row - 2
With ws1
    For i = 2 To lRow
        sht = .Range("B1") & .Range("B" & i)
            For Each sheet In targetWB.Sheets
                If sheet.Name = sht Then
                    With sheet
                        nRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
                        ws1.Range("A" & i).Copy Destination:=.Range("N" & nRow)
                        ws1.Range("C" & i).Copy Destination:=.Range("A" & nRow)
                        ws1.Range("D" & i).Copy Destination:=.Range("R" & nRow)
                        ws1.Range("E" & i).Copy Destination:=.Range("S" & nRow)
                        ws1.Range("F" & i).Copy Destination:=.Range("E" & nRow)
                        ws1.Range("G" & i).Copy Destination:=.Range("M" & nRow)
                        ws1.Range("H" & i).Copy Destination:=.Range("H" & nRow)
                        ws1.Range("I" & i).Copy Destination:=.Range("J" & nRow)
                        ws1.Range("J" & i).Copy Destination:=.Range("O" & nRow)
                        ws1.Range("K" & i).Copy Destination:=.Range("U" & nRow)
                    End With
                End If
            Next sheet
    Next i
End With
Application.ScreenUpdating = True


End Sub
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Worked okay in my test. So I'm going to point out the probable culprits: Are your workbooks named exactly as you have them in the code? Is FRBO (Private Apt).xls really an .xls file and not .xlsx or .xlsm? And does it contain a sheet named precisely "Sheet1" (no spaces, capitalized as shown)?
 
Last edited:
Upvote 0
Worked okay in my test. So I'm going to point out the probable culprits: Are your workbooks named exactly as you have them in the code? Is FRBO (Private Apt).xls really an .xls file and not .xlsx or .xlsm? And does it contain a sheet named precisely "Sheet1" (no spaces, capitalized as shown)?


Hi MrIfOnly

Yes, the file is there as well as the worksheet. It was working fine before I switch to another file, but I did rename the code. That's why I'm puzzled as well why it fails to work. :confused:

When the error occurs, the following code was highlighted.

lRow = ws1.Cells(Rows.Count, "B").End(xlUp).Row



 
Last edited:
Upvote 0
I can get the same error on that line, but only if there is no data in the worksheet.
 
Upvote 0
I suspect that targetWB is the active workbook when you get the error. If so Rows.Count will return 1048576, but as sourceWB is an xls file it only has 65536. try
Code:
lRow = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row - 2
 
Upvote 0
Cross posted https://www.excelforum.com/excel-pr...lication-defined-or-object-defined-error.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
Cross posted https://www.excelforum.com/excel-pr...lication-defined-or-object-defined-error.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.


Noted. My apology for cross posting.
 
Upvote 0
I usually get 1004 errors when the target path or target values are missing or not exactly the same as the code.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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