Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Sheet is not found/available Help

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •