If I have a worksheet that contains this:

Apples
Oranges
Pears

And I have another worksheet that contains:

Apples
Oranges
Pears
Tangerines

How do I scan the worksheet and find Tangerine isn't on the first
worksheet
and have it copy to the first worksheet without recopying Apples
Oranges Pears

I don't even know where to start this is a simple example of a more
complex worksheet.

Thanks
Charles


--
mrdata
------------------------------------------------------------------------
mrdata's Profile: http://www.excelforum.com/member.php...o&userid=17899
View this thread: http://www.excelforum.com/showthread...hreadid=528779

Maybe
http://www.rondebruin.nl/copy5.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"mrdata" wrote in message
...

If I have a worksheet that contains this:

Apples
Oranges
Pears

And I have another worksheet that contains:

Apples
Oranges
Pears
Tangerines

How do I scan the worksheet and find Tangerine isn't on the first
worksheet
and have it copy to the first worksheet without recopying Apples
Oranges Pears

I don't even know where to start this is a simple example of a more
complex worksheet.

Thanks
Charles


--
mrdata
------------------------------------------------------------------------
mrdata's Profile: http://www.excelforum.com/member.php...o&userid=17899
View this thread: http://www.excelforum.com/showthread...hreadid=528779

I tried the code on the website but I can't make it work will you tal
me through the steps I need to make this work?
I really need this for my job.

I think the code needs to be modified for my needs here is what I hav
thus far and it does nothing.

'This sub use the function LastRow
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim Str As String

Set WS1 = Sheets("Last Week") '<<< This is the destinatio
worksheet
Set WS2 = Sheets("L") '<<< This is the worksheet I want to pul
Data from
'A1 is the top left cell of your filter range and the header of th
first column
Set rng1 = WS1.Range("A5").CurrentRegion '<<< Change
Str = "Code" '<<< Change

'Close AutoFilter first
WS1.AutoFilterMode = False

'This example filter on the first column in the range (change th
field if needed)
rng1.AutoFilter Field:=5, Criteria1:=Str

With WS1.AutoFilter.Range
On Error Resume Next
' This example will not copy the header row
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1
.Columns.Count) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng2 Is Nothing Then
'Copy the cells
rng2.Copy WS2.Range("A" & LastRow(WS2) + 1)
'Delete the rows in WS1
rng2.EntireRow.Delete
End If
End With
WS1.AutoFilterMode = Fals

--
mrdat
-----------------------------------------------------------------------
mrdata's Profile: http://www.excelforum.com/member.php...fo&userid=1789
View this thread: http://www.excelforum.com/showthread.php?threadid=52877

WS1 is the sheet with data and ws2 the destination sheet

Try my test code first in a test workbook

--
Regards Ron de Bruin
http://www.rondebruin.nl


"mrdata" wrote in message
...

I tried the code on the website but I can't make it work will you talk
me through the steps I need to make this work?
I really need this for my job.

I think the code needs to be modified for my needs here is what I have
thus far and it does nothing.

'This sub use the function LastRow
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim Str As String

Set WS1 = Sheets("Last Week") '<<< This is the destination
worksheet
Set WS2 = Sheets("L") '<<< This is the worksheet I want to pull
Data from
'A1 is the top left cell of your filter range and the header of the
first column
Set rng1 = WS1.Range("A5").CurrentRegion '<<< Change
Str = "Code" '<<< Change

'Close AutoFilter first
WS1.AutoFilterMode = False

'This example filter on the first column in the range (change the
field if needed)
rng1.AutoFilter Field:=5, Criteria1:=Str

With WS1.AutoFilter.Range
On Error Resume Next
' This example will not copy the header row
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1,
Columns.Count) _
SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng2 Is Nothing Then
'Copy the cells
rng2.Copy WS2.Range("A" & LastRow(WS2) + 1)
'Delete the rows in WS1
rng2.EntireRow.Delete
End If
End With
WS1.AutoFilterMode = False


--
mrdata
------------------------------------------------------------------------
mrdata's Profile: http://www.excelforum.com/member.php...o&userid=17899
View this thread: http://www.excelforum.com/showthread...hreadid=528779

Here is the module code I am calling the sub import from a command
button on the destination worksheet "Last Week"

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A2"), _
Lookat:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Sub import()
'This sub use the function LastRow
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim Str As String

Set WS1 = Sheets("L") '<<< Change
Set WS2 = Sheets("Last Week") '<<< Change
'A1 is the top left cell of your filter range and the header of the
first column
Set rng1 = WS1.Range("A2:M350").CurrentRegion '<<< Change
Str = "Code" '<<< Change

How can I make it filter on every code number under the code column ?
Currently I have to change this to one of the code cloumn values such
as 400

Also I need the imported data to go on the last empty row they start
out at the top and push all the previously imported data down.
I appreciate you helping me with this I really need this for my job at
work .


'Close AutoFilter first
WS1.AutoFilterMode = False

'This example filter on the first column in the range (change the
field if needed)
rng1.AutoFilter Field:=1, Criteria1:=Str

With WS1.AutoFilter.Range
On Error Resume Next
' This example will not copy the header row
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1,
..Columns.Count) _
..SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng2 Is Nothing Then
'Copy the cells
rng2.Copy WS2.Range("A" & LastRow(WS2) + 1)
'Delete the rows in WS1
rng2.EntireRow.Delete
End If
End With
WS1.AutoFilterMode = False
End Sub


--
mrdata
------------------------------------------------------------------------
mrdata's Profile: http://www.excelforum.com/member.php...o&userid=17899
View this thread: http://www.excelforum.com/showthread...hreadid=528779

How can I make it filter on every code number under the code column ?
See
http://www.rondebruin.nl/copy5.htm#existing


--
Regards Ron de Bruin
http://www.rondebruin.nl


"mrdata" wrote in message
...

Here is the module code I am calling the sub import from a command
button on the destination worksheet "Last Week"

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A2"), _
Lookat:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Sub import()
'This sub use the function LastRow
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim Str As String

Set WS1 = Sheets("L") '<<< Change
Set WS2 = Sheets("Last Week") '<<< Change
'A1 is the top left cell of your filter range and the header of the
first column
Set rng1 = WS1.Range("A2:M350").CurrentRegion '<<< Change
Str = "Code" '<<< Change

How can I make it filter on every code number under the code column ?
Currently I have to change this to one of the code cloumn values such
as 400

Also I need the imported data to go on the last empty row they start
out at the top and push all the previously imported data down.
I appreciate you helping me with this I really need this for my job at
work .


'Close AutoFilter first
WS1.AutoFilterMode = False

'This example filter on the first column in the range (change the
field if needed)
rng1.AutoFilter Field:=1, Criteria1:=Str

With WS1.AutoFilter.Range
On Error Resume Next
' This example will not copy the header row
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1,
Columns.Count) _
SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng2 Is Nothing Then
'Copy the cells
rng2.Copy WS2.Range("A" & LastRow(WS2) + 1)
'Delete the rows in WS1
rng2.EntireRow.Delete
End If
End With
WS1.AutoFilterMode = False
End Sub


--
mrdata
------------------------------------------------------------------------
mrdata's Profile: http://www.excelforum.com/member.php...o&userid=17899
View this thread: http://www.excelforum.com/showthread...hreadid=528779