VBA required to keep specific sheets & rest all to be deleted

  • Hi friends,

    i have a folder with bulk files in it, Here my task is

    STEP1: I need to open each & every file & check the sheet name called START, in this sheet E column cells contains text as PROCEED & also other texts,

    i just need this entire row to be highlighted if the cell contains word PROCEED.


    STEP2:

    Then i need to go for B column in same sheet &

    here B column contains the sheet names (sheetA,sheetB,sheet1....etc)

    as per the sheet names in B column, the workbook also contains the sheets in the same names.

    Now i need to keep the sheets, only which are highlighted in above STEP1, rest all sheets can be deleted.


    Now the same process has to be repeated for the entire files in that folder.


    for better understanding i have attached the sample workbooks & manually higlighted some ROWS

    also changed the sheet name in RED, which are need & rest all to be deleted.


    i know its a big ask, but i m requesting some experts to help me.


    Thanks in Advance.not req.zip

  • Check it out.


    Note: Nothing like wasting an hour on a question that was already answer in a previous thread.

  • I answered almost the same question for you, that code needs adapting. I f you want to learn VBA then have a go.

    Sir, i m sorry , i m very new to VBA coding.....just started learning by recording & playing back the macros...

    then learning from your solution threads, your comment in codes are helpfull to me.....


    due to some office worloads i cant learn it full time :(

    i hope in somedays i will learn & try on my own....Thank you Sir

  • Code
    1. For Each sh In Sheets
    2. s = sh.Name
    3. If sh.Name <> ws.Name Then
    4. x = Application.WorksheetFunction.CountIf(ws.Range("B:B"), s)
    5. If x = 0 Then sh.Delete
    6. End If
    7. Next sh

    Sir,

    Here the code looks for the sheet name with Exact names in range (B:B)

    is it possible to find the approximate matches?


    for example:

    if the name appears as Sheet1 in B:B & sheet name comes as Sheet1-Approved

    since the sheet name contains additional words (-Approved) in its names, code not considering it.

    Please Help me Sir.

  • Dave's suggestion should work, but if you are deleting sheets you will get a warning before the deletion. You can avoid the warning by using Application.DisplayAlerts like this.


    Code
    1. For Each sh In Sheets
    2. s = sh.Name
    3. If sh.Name <> ws.Name Then
    4. x = Application.WorksheetFunction.CountIf(ws.Range("B:B"), "*" & s & "*")
    5. Application.DisplayAlerts = False
    6. If x = 0 Then sh.Delete
    7. Application.DisplayAlerts = True
    8. End If
    9. Next sh
  • Sir,

    Instead of Delete, i m using Hide option.

    Code
    1. For Each sh In Sheets
    2. s = sh.Name
    3. If sh.Name <> ws.Name Then
    4. x = Application.WorksheetFunction.CountIf(ws.Range("B:B"), "*" & s & "*")
    5. ''x = Application.WorksheetFunction.CountIf(ws.Range("B:B"), s)
    6. If x = 0 Then sh.Visible = False
    7. End If
    8. Next sh
  • Use very hidden, it's more secure because code is needed to unhide it


    Code
    1. If x = 0 Then sh.Visible = xlsheetveryhidden

    Which workbook contains the code that you are using?

  • Then I need to see that code

    this is the code i m using it in personal workbook Sir

  • Try this, from what I can see you need to explicitly state which workbook to work with