Sheet is not found/available Help

[fhan]

I am try to write a macro to open up several files and look for a particular sheet. If the sheet is found to copy it over another workbook. However, my dilemma comes when that sheet is not found/available. Is there a way to write into the macro to move to the next workbook if the sheet is not found?

This is what I got so far. Any suggestion is appreciated.

Windows("Book2").Activate
Sheets("Sheet2").Select
Sheets("Sheet2").Copy After:=Workbooks("Book1").Sheets(3)

Windows("Book3").Activate
Sheets("Sheet2").Select
Sheets("Sheet2").Copy After:=Workbooks("Book1").Sheets(4)

 

[daleyman]

Try this...



Sub transfer_sheets()


find_sheets_called = "Sheet2"
copy_sheets_to = "Book1"

For wn = 1 To Workbooks.Count
If Application.Workbooks(wn).Name <> copy_sheets_to Then
For sn = 1 To Workbooks(wn).Sheets.Count
If Workbooks(wn).Sheets(sn).Name = find_sheets_called Then
Workbooks(wn).Sheets(sn).Copy Before:=Workbooks(copy_sheets_to).Sheets(1)
End If
Next
End If
Next

End Sub

 

[Dave Hawley]

Hi

This may get you on your way. It makes use of the On Error Resume Next Statement, which means the code will continue if it encounters a non existing sheet.

Dim wBook As Workbook

On Error Resume Next
For Each wBook In Application.Workbooks
wBook.Sheets("Sheet2").Copy After:=Workbooks("Book1.xls").Sheets(3)
Next wBook
On Error GoTo 0