On Fri, 13 Feb 2009 06:21:02 -0800, Sue
wrote:

Hi
I have a folder on the desktop named CARD within the folder there are 20
single sheets all with a different name e.g. OB1, OB2, OB3 etc is it possible
in VBA to copy all the named sheets into a new workbook named 'Sue' in the
correct order of the sheet tab Ob1, OB2, OB3 etc and at the same time delete
sheets 1 to 4 that open in the new workbook 'Sue' I could then assign the
macro to a CommandButton.

Sub MakeSue()

Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sh As Worksheet
Dim sFname As String

Const sPATH = "C:\Documents and Settings\Dick\Desktop\CARD\"

Set wbDest = Workbooks.Add

sFname = Dir(sPATH & "OB*.xls")

Do While Len(sFname) 0
Set wbSource = Workbooks.Open(sFname)
wbSource.Sheets(1).Copy , wbDest.Sheets(wbDest.Sheets.Count)
wbDest.Sheets(wbDest.Sheets.Count).Name = Replace(wbSource.Name,
".xls", "")
wbSource.Close False
sFname = Dir
Loop

Application.DisplayAlerts = False
For Each sh In wbDest.Worksheets
If Not sh.Name Like "OB*" Then
sh.Delete
End If
Next sh
Application.DisplayAlerts = True

End Sub

Change the path to point to your desktop

--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com