Macro Stops Working After First Run

[rob51852]

Hi All,

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?

Thanks

 

[RoryA]

That code would only work if Data is the active sheet. It should be written as:

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

 

[RoryA]

Also, you should be familiar with the forum rules on cross-posting by now, so please follow them in future. Thanks.

 

[rob51852]

Thanks Rory. This works perfectly (and point taken).

 

[RoryA]

Glad to help.