Will not copy to City-Panmure
 

Hello from Steved

I have no I'dea as to what I've not done.
The below will filter City but it will not copy into City - Panmure Sheet

Please explain to me what is reqiured for this to function Thankyou.

Sub Copy_With_AutoFilter_2()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim Str As String
Set WS1 = Sheets("Trip missed")
Set WS2 = Sheets("City - Panmure")
Set rng1 = WS1.Range("A1").CurrentRegion
Str = "City"
WS1.AutoFilterMode = False
rng1.AutoFilter Field:=7, Criteria1:=Str
With WS1.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng2 Is Nothing Then
rng2.Copy WS2.Range("A" & LastRow(WS2) + 1)
End If
End With
WS1.AutoFilterMode = False
End Sub

Will not copy to City-Panmure
 

Only problem I would see is if you don't get any visible rows when you
filter on City.

The code worked fine for me with some actual rows to copy.

--
Regards,
Tom Ogilvy

"Steved" wrote in message
...
Hello from Steved

I have no I'dea as to what I've not done.
The below will filter City but it will not copy into City - Panmure Sheet

Please explain to me what is reqiured for this to function Thankyou.

Sub Copy_With_AutoFilter_2()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim Str As String
Set WS1 = Sheets("Trip missed")
Set WS2 = Sheets("City - Panmure")
Set rng1 = WS1.Range("A1").CurrentRegion
Str = "City"
WS1.AutoFilterMode = False
rng1.AutoFilter Field:=7, Criteria1:=Str
With WS1.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng2 Is Nothing Then
rng2.Copy WS2.Range("A" & LastRow(WS2) + 1)
End If
End With
WS1.AutoFilterMode = False
End Sub

Will not copy to City-Panmure
 

Thanks Tom

"Tom Ogilvy" wrote:

Only problem I would see is if you don't get any visible rows when you
filter on City.

The code worked fine for me with some actual rows to copy.

--
Regards,
Tom Ogilvy

"Steved" wrote in message
...
Hello from Steved

I have no I'dea as to what I've not done.
The below will filter City but it will not copy into City - Panmure Sheet

Please explain to me what is reqiured for this to function Thankyou.

Sub Copy_With_AutoFilter_2()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim Str As String
Set WS1 = Sheets("Trip missed")
Set WS2 = Sheets("City - Panmure")
Set rng1 = WS1.Range("A1").CurrentRegion
Str = "City"
WS1.AutoFilterMode = False
rng1.AutoFilter Field:=7, Criteria1:=Str
With WS1.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng2 Is Nothing Then
rng2.Copy WS2.Range("A" & LastRow(WS2) + 1)
End If
End With
WS1.AutoFilterMode = False
End Sub