Excel Macro Help

[yaroslav89]

Hello everyone,
Just quickly, I am trying to create a macro where it will copy specific range in various workbooks and paste in a new workbook. My problem is that when I run a macro not all workbooks available at the time of running a macro. So Microsoft gives an error message "file either moved or deleted." Is there a way to go about if workbook is not found move to the next workbook. Below code works fine if all workbooks are in the specified path, but in my experience this is not the case. Some will be missing. In advance any help much appreciated!

Dim x As Workbook, y As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet

Set x = Workbooks.Open("C:\Users\Test 1", Password:="fun")
Set y = Workbooks.Open("C:\Users\Result 1", Password:="fun2")

Set ws1 = x.Sheets("Sheet1")
Set ws2 = y.Sheets("Sheet1")

ws1.Cells.Range("A1:R6").Copy ws2.Range("A1")

y.Close True
x.Close False

Set x = Workbooks.Open("C:\Users\Test 2", Password:="fun")
Set y = Workbooks.Open("C:\Users\Result 1", Password:="fun2")

Set ws1 = x.Sheets("Sheet1")
Set ws2 = y.Sheets("Sheet1")

ws1.Cells.Range("A1:R6").Copy ws2.Range("A7")

y.Close True
x.Close False

 

[JoeMo]

After the second Dim line add this line:

On Error Resume Next

 

[jim may]

Insert 2 new lines currently Before the current Error-Line

and enter:

On Error
Resume Next

 

[yaroslav89]

Thanks Joe!

 

[yaroslav89]

Thanks Jim!

 

[JoeMo]

Thanks Joe!

You are welcome - thanks for the reply.