Run-time error '1004': Application-defined or object-defined error. (copy and pasting results )

Question

I am trying to put results from sheet a to different sheets accordingly.

Sub update()
Dim cell As String, sheet As String, rcdate As String, row As Integer
row = 2
ThisWorkbook.Sheets("Auto Generate V2").Activate
cell = CStr(ThisWorkbook.Sheets("Auto Generate V2").Cells(row, 10).value)
Do While cell <> ""
sheet = Left(cell, 10)
rcdate = Right(cell, 3)
ThisWorkbook.Sheets(sheet).Activate
ThisWorkbook.Sheets(sheet).Range(rcdate).value =                           ThisWorkbook.Sheets("Auto Generate V2").Cells(row, 5).value
row = row + 1
cell = CStr(ThisWorkbook.Sheets("Auto Generate V2").Cells(row, 10).value)
Loop
End Sub

The error is at ThisWorkbook.Sheets(sheet).Range(acdate).value = ThisWorkbook.Sheets("Auto Generate V2").Cells(row, 5).value How do I solve it

What is a typical value in column J of the Auto Generate V2 worksheet? Do any values get transferred or does it choke on the first one? — user4039065
They are strings for location of which sheet and which cell , 5 values get transferred and after that it choke. — justforfun
I solved it , thanks jeeped . Instead of combining two values in a cell for location , i should have split them up — justforfun

Unknown Unknown · Accepted Answer · 2015-07-26T12:46:03

The unnecessary Worksheet.Activate method are no doubt contributing to an unstable environment. I've modified your routine to remove dependence on the .ActiveSheet.

Sub update()
    Dim cell As String, sht As String, rcDate As String, rw As Long
    rw = 2
    With ThisWorkbook.Worksheets("Auto Generate V2")
        cell = Trim(CStr(.Cells(rw, 10).Value))
        Do While cell <> ""
            sht = Left(cell, 10)
            rcDate = Right(cell, 3)
            'Debug.Print .Parent.Worksheets(sht).Range(rcDate).Address(external:=True)
            .Parent.Worksheets(sht).Range(rcDate) = .Cells(rw, 5).Value
            rw = rw + 1
            cell = Trim(CStr(.Cells(rw, 10).Value))
        Loop
    End With
End Sub

If you are still having trouble, consider closing everything off and rebooting the computer. A series of crashes made during the development of a sub can lead to an underlying unstable compiler environment. More than once a simple machine restart has fixed an unknown problem.

Run-time error '1004': Application-defined or object-defined error. (copy and pasting results )

1 Answers