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

[whoosh]

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.

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

 

[MrIfOnly]

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

 

[whoosh]

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.

When the error occurs, the following code was highlighted.

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

 

[MrIfOnly]

I can get the same error on that line, but only if there is no data in the worksheet.

 

[whoosh]

I can get the same error on that line, but only if there is no data in the worksheet.

Hmmm... I have data on my worksheet.
Quite frustrating why it's not working.

 

[Fluff]

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

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

 

[Fluff]

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.

 

[whoosh]

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.

 

[whoosh]

Anyone???

 

[RasGhul]

I usually get 1004 errors when the target path or target values are missing or not exactly the same as the code.