James:

Here is a macro I use to sort a worksheet based on an InputBox. I have
added code to copy the filtered range, open a new workbook and paste.
Change "Sheet1" to your sheet name. Change the SaveAs "yourFilePath" and
"YourFileName". It is a mixture of a couple of existing macros, so I won't
be surprised if there's a speed bump or two.

Sub SortMe()

Dim MyTarget As String
Dim wb1 As Workbook
Dim wb3 As Workbook

Set wb1 = ActiveWorkbook

' Clear previous sort
Sheets("Sheet1").AutoFilterMode = False

With Cells
.EntireColumn.Hidden = False
.EntireRow.Hidden = False
End With

Range("A1").Select

' Choose series
MyTarget = Application.InputBox("Which series?")

If MyTarget = "" Then GoTo Bye

Application.ScreenUpdating = False

' Filter for vehicle
Selection.AutoFilter
' This uses Field 3, which is Column C. Adjust to suit.
Selection.AutoFilter Field:=3, Criteria1:= MyTarget

' Copy used range
wb1.Sheets("Sheet1").UsedRange.Copy

' Turn off alerts
Application.DisplayAlerts = False

' Creates a new workbook
Set wb3 = Workbooks.Add
' Ensures 3 sheets in new book
Do While wb3.Worksheets.Count < 3
wb3.Worksheets.Add
Loop

' Copies the current file and pastes into the new workbook
wb3.Sheets("Sheet2").Range("a1").PasteSpecial xlPasteValues
wb3.SaveAs Filename:= "C:\YourFilePath\YourFileName.xls"

' Reset worksheet
' Unhide everything
wb1.Activate
Sheets("Sheet1").Activate
Sheets("Sheet1").AutoFilterMode = False

With Cells
.EntireColumn.Hidden = False
.EntireRow.Hidden = False
End With

Application.CutCopyMode = False

Range("A1").Select

Application.ScreenUpdating = True
Application.DisplayAlerts = True

MsgBox "I'm done!"

Bye:
End Sub


"JamesMantle"
wrote in message
...

Ed, Jason, Tom - many thanks.

Having a problem with the pivot table approach - that option is greyed
out in the Data window whenever I select anything - any way I could
rectify that?

Ed, if I took your approach, in setting up a macro with the worksheet
open (presuming I'm only going to run the macro to extract data on my
computer), where is the "input box" I can type the shortnames into? Any
chance you could explain the last paragraph of what you wrote as I'm
none too wiser at the minute.

Many thanks guys.

James


--
JamesMantle
------------------------------------------------------------------------
JamesMantle's Profile:
http://www.excelforum.com/member.php...o&userid=26632
View this thread: http://www.excelforum.com/showthread...hreadid=399046