[Claus Busch]

Hi E-on

Am Tue, 5 Jun 2012 20:32:57 +0000 schrieb E-on:

If my understanding is correct. This below only applies to "Overview"
Sheet. But as I just explained to Bob, I have 12 sheets.

now it's working with each sheet of your workbook:

Sub Copytom()
Dim rng As Range, i As Long, ws As Worksheet
Dim sh As Worksheet, LRow As Long

On Error Resume Next
Set sh = Worksheets("New")
If Not sh Is Nothing Then
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = False
End If

Set sh = Sheets.Add
sh.Name = "New"

For Each ws In Worksheets
With ws
If ws.Name < "New" Then
.Range("A2").AutoFilter Field:=5, Criteria1:= _
"Stockwell Motors"
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("F3:G" & LRow).Copy _
Worksheets("New").Range("A" & _
Rows.Count).End(xlUp).Offset(1, 0)
.AutoFilterMode = False
End If
End With
Next

End Sub

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2