Anybody spot the error in this code?

[stevelake]

Hi

The code below is supposed to run when the contents of A7 are changed. Unfortunately it stops at ... "Rows("5:1000").Select"

With the message...

Error 1004 "Select method of range class failed"

If l take out the line "If Target.Address = "$A$7" Then"
and assign the rest of the code to a button then the code runs well so l guess it must be an error in that line


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$7" Then
Sheets("Data Entry").Activate
Rows("5:1000").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=Sheets("Viewing").Range("A7").Value

ActiveSheet.UsedRange.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Copy Sheets("Viewing").Range("A42")

ActiveSheet.AutoFilterMode = False
Sheets("Data Entry").Select
Range("A6").Select

Sheets("Viewing").Select
Range("A42").Select


End If

End Sub

All help and advice is greatly appreciated

Thanks

Steve

 

[Lewiy]

The line is trying to select rows from the original sheet after already activating the Data Entry sheet, you need to qualify that you want it to select rows from Data Entry:

Sheets("Data Entry").Rows("5:1000").Select

Hope that helps!

 

[stevelake]

Yes - Perfect thanks!