Opening multiple workbooks through code and copying values

[Guest]

Help! the following code 'kinda sorta' does what I want, but it has 3 problems.

1. Instead of going on to the next file if the file doesn't exist, it pastes
the data from the previous file.

2. It closes the workbook into which I am compiling data instead of just the
ones I am copying from.

3. Everytime it closes a file, it asks the question about 'large amounts of
data in the clipboard.'

Thanks,
Pam

Here is the code:


Private Sub cmdCreate_Click()

On Error GoTo Report_Err

Dim myPath As String
Dim myReport As Object
Dim mySheet As String
Dim myRow As Integer
Dim xlBook As Excel.Workbook

'Opens workbooks and copies values from the sheet named 'Numbers' found in
'each workbook into the workbook named DOCSEMAILREPORT

myRow = 18

'Loop through rows in the worksheet named Create until it reaches a
blank row.
'Column 8 contains the complete path with file name of the file to be
opened,
'column 6 contains the name of the file, and column 2 contains the name
of the
'worksheet where the values will be pasted.

Do While Sheets("Create").Cells(myRow, 2).Value <> ""
myPath = Sheets("Create").Cells(myRow, 8) 'Path
myReport = Sheets("Create").Cells(myRow, 6) 'name of file
mySheet = Sheets("Create").Cells(myRow, 2) 'name of worksheet to
paste values into
Set xlBook = myReport


'If the file exists, open it and copy the values of a range of cells
in the
'worksheet named Numbers. Paste them into the correct sheet in the
'DOCSEMAILREPORT workbook

If xlBook.FileExists(myPath) Then

Workbooks.Open Filename:=myPath
Sheets("Numbers").Select
Sheets("Numbers").Range("A5:K23").Select
Selection.Copy
Application.WindowState = xlMinimized
Windows("DOCSEMAILREPORT v1.xls").Activate
Sheets(mySheet).Select
Sheets(mySheet).Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows(myReport).Activate
ActiveWindow.Close
myRow = myRow + 1

'If the file does not exist, increment the row variable and continue
Else
myRow = myRow + 1
End If

Loop

Report_Err:
Resume Next

End Sub

 

[Guest]

hi,
two things.
on your point 1.
application.cutcopymode = false
put this after paste. to clear the clipboard
on your point 2.
instead of "Windows(myReport).Activate"
put ActiveWindow.ActivateNext (assuming you only have 2
files open at that time. if you have more that 2 files
open, you will have to call the file you want to close by
name.)
on your point 3.
application.cutcopymode = false should take care of that
too.