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 ...


    Quote


    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 "Thumbs Up" 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


    Code
    1. Sub KeepCF()
    2. Worksheets.Select
    3. Cells.Select
    4. On Error Resume Next
    5. With Selection.SpecialCells(xlCellTypeAllFormatConditions)
    6. .Interior.Color = .DisplayFormat.Interior.Color
    7. .FormatConditions.Delete
    8. End With
    9. On Error GoTo 0
    10. 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.

    Files

    • Sheet1.xlsx

      (36.87 kB, downloaded 52 times, last: )
  • This line in my code closes the workbook anfter saving

    Code
    1. oWb.Close True


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


    Code
    1. .Interior.Color = .DisplayFormat.Interior.Color


    Is this any better