This is a follow-up on an early posting:

http://groups.google.com/group/micro...44f1f3bd616881

Interesting .... GooglesGroups says there's FOUR messages on that one,
but I can only see 3 ....

Anyways, I think I'm confusing myself. I'll post my current code
below, but to explain what I want to do:
There are two possible situations - update ALL store data OR only
update some store's data. My current code opens (one at a time) all
workbooks in a specified folder and copy/pastes a specified column
into the summary workbook. This works well (thanks Bernie!).

With scenario two, the user should be able to specify which stores to
update -- all others should remain the same. I've taken Dave's
suggestion and am not using checkboxes. I have my list of stores in
one column and in the next column, the user will choose 'yes' or
'no' (uses validation). If a user marks StoreXXX as 'yes', the code
should ONLY open StoreXXX's file to copy/paste from. Format for the
source_file name is 'Forecast_StoreXXX_pd507.xls' After the macro
runs, all yes/no cells should be reset to 'no'.

Further, there will be an option (above the store numbers) to select
'update all' -- this should fire the macro I already have (code
below).

Could someone please help me to do this? Code optimization on my
existing code is certainly welcome -- a whole lot of learning on this
one already, looking forward to more!

Existing code: [Excel2002 on XP]

Sub FetchStoreData_Click()
Dim MyPath As String, getstore 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 Workbook, sh
As Workbook
Dim sourceRange As Range, destrange As Range, myC As Range

MyPath = "\\r...\...\"

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

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.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
basebook.Sheets("Current Store
FCs").Range("C5:AG500").ClearContents 'clear all cells on all
sheets

'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(MyPath & MyFiles(Fnum), 0,
True)

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

' Isolates the store number from the workbook name
getstore = mybook.Sheets("Dashboard").Range("E13").Value
getstore = Format(getstore, "000")

mybook.Sheets("P&L Acct Detail").Unprotect ("busnav")
Set sourceRange = mybook.Sheets("P&L Acct
Detail").Range("J5:J500")

Set myC = basebook.Worksheets("Current Store FCs"). _
Range("3:3").Find(getstore, LookIn:=xlValues,
LookAt:=xlWhole)

If Not myC Is Nothing Then
Tcol = myC.Column
Else
MsgBox getstore & " wasn't found"
' it would be great if the code could NOT put
up a msgbox (as this interrupts the code), just close and go to next
file
' but at the very end, a msgbox could pop up
listing ALL of the files that couldn't be updated
'Other action to take when getstore is not
found
End If

Trange = Cells(5, Tcol).Resize(496, 1).Address

Set destrange = basebook.Sheets("Current Store
FCs").Range(Trange)

destrange.Value = sourceRange.Value

mybook.Close savechanges:=False

Next Fnum
End If

Application.StatusBar = False

MsgBox "Matrix is Updated!"

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

End Sub

Thanks VERY much for your time to help me with this code!
br//ray