CODEWITHSUNDEEP
excelvba
rob
rob

Reputation: 317

Macro Stops Working After First Run - Run-time Error 2004 Application-defined or Object-defined error

I am trying to put together a macro that finds a name in a column header then copies that column if the header matches.

It works the first time I run it but when the pasted column is deleted and the macro is run again I get a

Run-time Error 2004 Application-defined or Object-defined error

on this line:

Set sRange = Sheets("Data").Range("C1", Cells(1, LastCol))

Full code:

Sub Copy()

Dim Cell As Range, sRange As Range, Rng As Range

    LastCol = Sheets("Data").Cells(1, Columns.Count).End(xlToLeft).Column

        Set sRange = Sheets("Data").Range("C1", Cells(1, LastCol))

            With sRange

                Set Rng = .Find(What:="Chennai", _
                                After:=.Cells(1), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False)

                    If Not Rng Is Nothing Then

                        Lastrow = Sheets("Data").Cells(Rows.Count, Rng.Column).End(xlUp).Row

                            Sheets("Data").Range(Rng, Cells(Lastrow, Rng.Column)).Copy _
                                Destination:=Sheets("Summary").Range("A7")
                    End If
            End With
End Sub

Can anyone see the issue?

Upvotes: 0

Views: 73

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

Cells(1, LastCol)) has no worksheet specified. Therfore it is the same as ActiveSheet.Cells(1, LastCol)) and if Sheets("Data") is not the ActiveSheet this fails.

It should be 

Set sRange = Worksheets("Data").Range("C1", Worksheets("Data").Cells(1, LastCol))

or even better 

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")

Set sRange = ws.Range("C1", ws.Cells(1, LastCol))

Also I recommend to use Worksheets for worksheets as Sheets can also contain chart sheets etc.

Same problem in the end where Cells(Lastrow, Rng.Column) has no worksheet specified:

ws.Range(Rng, ws.Cells(Lastrow, Rng.Column)).Copy _
                            Destination:=Worksheets("Summary").Range("A7")

Make sure you never have a Cells, Range, Rows or Columns object without a worksheet specified. Or Excel might take the wrong worksheet.

In the end I would do something like (note that all variables should be declared, use Option Explicit:

Option Explicit 

Public Sub Copy()
    Dim wsSrc As Worksheet 'source worksheet
    Set wsSrc = ThisWorkbook.Worksheets("Data")

    Dim wsDest As Worksheet 'destination worksheet
    Set wsDest = ThisWorkbook.Worksheets("Summary")

    Dim LastCol As Long
    LastCol = wsSrc.Cells(1, wsSrc.Columns.Count).End(xlToLeft).Column

    Dim sRange As Range
    Set sRange = wsSrc.Range("C1", wsSrc.Cells(1, LastCol))

    Dim Rng As Range
    Set Rng = sRange.Find(What:="Chennai", _
                          After:=sRange.Cells(1), _
                          LookIn:=xlValues, _
                          LookAt:=xlWhole, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False)

    If Not Rng Is Nothing Then
        Dim LastRow As Long
        LastRow = wsSrc .Cells(wsSrc.Rows.Count, Rng.Column).End(xlUp).Row

        wsSrc.Range(Rng, wsSrc.Cells(LastRow, Rng.Column)).Copy _
                                Destination:=wsDest.Range("A7")
    End If
End Sub

Upvotes: 3

Related Questions