It looks you are missing an End If

sourceRange.Copy
destrange.PasteSpecial xlPasteAll
End If ' this was missing
Next ws


RBS

"Ray" wrote in message
oups.com...
Hope someone with 'eagle eyes' can help on this one ...

Here's my code:
Sub UpdStoData_Click()
Dim Path As String, stonum As String, FilesInPath As String
Dim MyFiles() As String, Trange As String, Tcol As Integer
Dim SourceRcount As Long, x As Long, Fnum As Long, total As Long
Dim mybook As Workbook, basebook As Workbook, ws As Worksheet, Sh
As String
Dim sourceRange As Range, destrange As Range, myC As Range

Path = "\\Retus100-nt0009\common\US OPS Projects\from Stores\"

'Add a slash at the end if the user forget it
If Right(Path, 1) < "\" Then
Path = Path & "\"
End If

' 'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(Path & "*.xls")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

' On Error GoTo CleanUp

Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set basebook = ThisWorkbook

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath < ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
total = Fnum

If Fnum 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(Path & MyFiles(Fnum), 0, True)

Application.StatusBar = "Now processing File " & Fnum & "
of " & total

' Isolates the store number from the workbook name
stonum = mybook.Sheets("Home").Range("c3").Value
stonum = Format(stonum, "000")

For Each ws In mybook.Worksheets
ws.Activate
If ws.Name < "Home" Then

Sh = ws.Name

Set sourceRange = ws.Range("m7:m100")

Set myC = basebook.Sh.Range("m5:ba5").Find(stonum,
LookIn:=xlValues, LookAt:=xlWhole)

If Not myC Is Nothing Then
Tcol = myC.Column
Else
MsgBox stonum & " wasn't found"
GoTo CleanUp
End If

Trange = Cells(8, Tcol).Resize(93, 1).Address

Set destrange = basebook.ws.Range(Trange)

sourceRange.Copy
destrange.PasteSpecial xlPasteAll
Next ws

mybook.Close savechanges:=False

Next Fnum
End If

Application.StatusBar = False

MsgBox "Store FCs are Updated!"

CleanUp:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.StatusBar = False

End Sub

'Basebook' is a summary workbook, designed to collect data from approx
30 external workbooks -- these external WBs are identical in setup,
including sheet names.

The code opens up each of the external workbooks and copies the
specified range from SheetA in mybook to SheetA (remember, sheet names
are identical) in Basebook .... using StoNum to identify the proper
column to paste the data into.

The problem is that I keep getting 'Next without For' errors and I
don't see where the problem is -- I have them all matched up, I
think. can you help?

TIA,
Ray