Looping trough all workbooks in a folder an their worksheet. Action is to paste special, so that there is no formula

  • Hello, I'm trying to implement a macro that would loop trough files in a folder you can select every time the macro is ran. Maybe someone could Help? I am not that familiar with code yet. The main task for it is to paste special the all the sheets (Some don't need) so that the formula disappears and after that delete columns N and forward and then move to the next workbook until it's done


    Task explained:

    I got a file of multiple (50) Excels that should sent to customers. For each one now I have pasted special so that the formula is removed from the cells and after that deleted unnecessary columns with sensitive information like pricing or notes. I would need the macro to first open a workbook in the selected folder. Then paste special (formula removed from whole sheet) and do that for each sheet in workbook with few exceptions. After that repeat the process but for deleting columns from N - AA for each sheet. After that close workbook and repeat for all files in the selected file.


    Here's some macros that I like and could be useful (Maybe combining them and making them compatible with each other would work?):

    Display Less
    Code: Avoiding certain sheet names


  • do that for each sheet in workbook with few exceptions

    What are the exceptions?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try this macro. Change the sheet array to suit your needs.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • It is difficult to test a macro without having access to your workbooks. Could you post a copy of one or two of your workbooks (de-sensitized if necessary)?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Sorry for the delay, I've been a bit busy. I've tested the files now and there's like 5 of 50 that won't work. Otherwise it works like a charm.

    I've tried searching where he error would be and come to a solution and modified the code to it. Here's the explanation:


    The macro didn't work because in the range of M to AA was information to close to the table (text, notes and random numbers like prices etc.). The range that will be deleted. When I removed the text from that range before running the macro it works for the Workbooks that before gave an error. The range that it needs to paste special is From B1 To M500. I believe that because it wasn't a specific range, but a usedrange, the macro got confused)



    - Delete a list of sheet names like (Pricing, Cover, Important, notes)


    Can the code below be modified to be functioned because they don't work / haven't found a solution to it?


    1. Delete all pictures

    -. shapes.delete doesn't work +

    2. Sheet deleting instead of avoiding (Delete Pricing, Cover, Important, notes)

    - I did not find code that would work for the sheet deleting for specific names. Maybe instead pf sheet voiding it would just delete them?


    Here's the modified code





    __________________________________________________________________________________________________________________________________________________________________

    Unrelated to this but would be useful in Call function combined with the previous macro is selecting a folder from point A where the files are and moving them to Point B where it would Paste special.


    I've got this Macro below and it work fine. I just don't know how to modify it. Basically now it requires a folder path hard coded into the code but I would like to work like Paste special macro that makes you select the folder. The only exception here is that it needs to choose from where and where to.

  • Try:

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I'm getting Error 9 - Subscript out of range .


    One thing I noticed is that is this: Because the sheet deleting happens before the pasting special, that will break the formula, right? Can be done so that it would run after all the pasting and deleting? When I removed the error part it worked great!


    I'm also getting errors if file is protected, can that be removed before macro is ran and a protected back on after it's done?


    ________________________________________________________________________________________________________________________________________________________________


    After that is resolved how can the pasting files from point a to b be separated from all of that? I have another project I could use the call function for it that would be helpful. Is it possible to remove to warning symbol if the file already exists in current folder? Because the action this is going to be used for requires the files the replaced by fresh ones. Is there in a macro like a true or false place I can turn it of and on when the situation suits it?


  • Is the workbook protected or are the worksheets protected or both? If they are protected, are they protected with or without a password?

    how can the pasting files from point a to b be separated from all of that?

    Please describe in detail what you mean by this?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Second question was unrelated to this problem. In the second question I meant that I would need the copy files from point a to B with no editing to the files, so that it let's me choose the path. It was demo that was not working (I just removed row deleting and pasting special)


    The main problem was this for the macro u sent me:


    "I'm getting Error 9 - Subscript out of range .


    One thing I noticed is that is this: Because the sheet deleting happens before the pasting special, that will break the formula, right? Can be done so that it would run after all the pasting and deleting? When I removed the error part it worked great!


    I'm also getting errors if file is protected, can that be removed before macro is ran and a protected back on after it's done?"

  • I would need the copy files from point a to B with no editing to the files

    I'm sorry but I still don't understand what you want to do.


    Also, please answer the following questions:

    Is the workbook protected or are the worksheets protected or both? If they are protected, are they protected with or without a password?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • line 23 to 28, line 48 + removing the warning when replacing a file with a same name are the changes I want. They don't work but hope this gives you the idea what I was looking for

  • You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hello, this work well! The problem I'm facing is with line 38. It works, but only if the workbooks has the sheet name otherwise the code will debug. If I for instance have pricing and Important in the list, but the workbook only has a pricing sheet, the code stops because it could not find a sheet named important. But if I only run the code with pricing in the list, it works well until it comes across a files with no pricing sheet. Hope you get what I'm going after.


    Is there a way to make the code scan trough the names listed (Pricing", "Cover", "Important", "notes). Then delete the ones that are in the workbook and not debug? Because one workbook has a sheet named important and another might have cover.

  • Try:

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Solution verified! It works great.


    If I could ask one more thing is with line 6 to 9. Could that location be hard coded into the code, so that I don't have to pick the folder. The location is: "C:\Desktop\Excel_Files_1".


    The destination folder is fine as it is. It changes every time new info is in the original ones.

  • Try:

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Thanks!


    I found this code that removes all excel tab colors (Could this be implemented?):

  • You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

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