Hi

Just to say many thanks to all now working exactly
as required.
--
Many Thanks

Sue


"Dave Peterson" wrote:

The sfname variable only contains the filename--it doesn't include the path.

Set wbSource = Workbooks.Open(sFname)
should be:
Set wbSource = Workbooks.Open(spath & sFname)

Sue wrote:

Hi
Error Message on Set wbSource line see below!!!

Sub MakeSueA()

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

Const sPATH = "C:\Documents And Settings\Sue\Desktop\CARD\"

Set wbDest = Workbooks.Add

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

Do While Len(sFname) 0
Debug.Print sFname
Set wbSource = Workbooks.Open(sFname) '<<<<<<Message Box: Run-Time 1004 -
OB1.xls could not be found
Debug.Print wbSource.Name
wbSource.Sheets(1).Copy , wbDest.Sheets(wbDest.Sheets.Count)
Debug.Print "Count: " & wbDest.Sheets.Count
wbDest.Sheets(wbDest.Sheets.Count).Name = Replace(wbSource.Name, ".xls", "")
wbSource.Close False
sFname = Dir
Loop

End Sub

As before Immediate Window -- Displays OB1.xls

--
Many Thanks

Sue

"Dick Kusleika" wrote:

On Fri, 20 Feb 2009 12:37:01 -0800, Sue
wrote:


Immediate Window -- Displays OB1.xls

Run this


Sub MakeSue()

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

Const sPATH = "C:\Documents And Settings\Sue\Desktop\CARD\"

Set wbDest = Workbooks.Add

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

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

End Sub

and tell me what the Immediate Window says.
--
Dick


--

Dave Peterson