Run the Code on All workbooks a folder contains

  • I have been trying to make a code that would run on all sheets a folder contains. But i am stuck that how to do this. I have wrote some line and gather information via google but difficult to create this one.

    Here is the code which is required to run on all workbooks with sheet name i.e. DATA

    Here is the second one which provide the option to select the folder.

    Your help will be highly appreciated.

  • Hello,

    You should clarify what is exactly your objective ...


    all sheets a folder contains

    - Worksheets are located in Workbooks

    - Workbooks are located in a Folder

    But Sheets cannot be located in a Folder ...

    Hope this will help


    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hi, Carim.

    Just trying to keep the conditional formatting colors then code will delete all Conditional formatting. My below code is just working for single Workbook.Worksheets("DATA") at a time.

    I am looking for a way to run this code on multiple workbooks at once by selecting a folder.

  • This will be faster on the whole workbook

    Sub KeepCF()
        On Error Resume Next
        With Selection.SpecialCells(xlCellTypeAllFormatConditions)
            .Interior.Color = .DisplayFormat.Interior.Color
        End With
        On Error GoTo 0
    End Sub

    Do you want to browse for the folder or will it be fixed?

  • Try this, I haven't tested it so use some dummy workbooks

  • It is really working like rocket Thanks.

    1. But there is some problem that it does not save the changes and close the workbooks after running the code.

    2. when i run the code it makes the sheet color black i have attached a sheet for example. I have run the above code that you provided for single workbook does same for single workbook.

    I have closed each workbook one by one after running the code.

  • This line in my code closes the workbook anfter saving

    oWb.Close True

    This line is from your code so I assumed it worked.

     .Interior.Color = .DisplayFormat.Interior.Color

    Is this any better

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!