http://www.rondebruin.nl/copy2.htm

I think Mark wanted to post this link
http://www.rondebruin.nl/copy5.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"NoodNutt" wrote in message ...
G'day Catherine

You may find this link helpful

http://www.rondebruin.nl/copy2.htm

here is a copy of my code that I modified to suite my purposes based on
Ron's Code.

Sub Split_Data()

Dim SourceSheet As Worksheet
Dim DestinationSheet As Worksheet
Dim rng As Range
Dim rng2 As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set SourceSheet = Sheets("Data")
Set rng = SourceSheet.Range("A8:M" & Rows.Count)
Set DestinationSheet = Sheets("NSW")

SourceSheet.AutoFilterMode = False
rng.AutoFilter Field:=1, Criteria1:="=SYD"

SourceSheet.AutoFilter.Range.Copy
With DestinationSheet.Range("A5")
.PasteSpecial xlPasteValues
Application.CutCopyMode = False
.Select
End With
End Sub

This peice of code copies only rows that match "Syd" in the first column and
copies it to my new sheet NSW, you can also create a new sheet.

HTH
Mark.