Use all ifs in a Macro

  • Hello everyone,

    First of all, sorry for any mistakes, im a beginner(started learning 2 weeks ago before joining the internship im currently in).


    So, i have a VBA Macro in which i created a filter for each department in my company, and it works perfectly fine when i want a singular department... but i cant get it too automate all departments at once. My boss told me to create some another "while" but i cant get it to work as i did with the first one. The idea is, i need this code to use all departments at once, to open all the tabs with emails so i just have to confirm it, instead of doing each department at once.


    Please let me know if i didnt give enough information... im kinda desperate lol.


  • It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

    You can say "THANK YOU" for help received by clicking the :thumbup: 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,


    As indicated by Mumps ;) ... attaching a file ( without confidential data ) would help a lot ...:)


    It would seem that you only need to insert a Loop of your various "departamentos" which are probably listed in the Column B of your worksheet named "workflow" ...


    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:)

  • I would have a ComboBox to select the departments from. Something like


  • Hey guys, thank you so much already for the replies!! i've added the table in this reply, gonna explain a little bit more about what i need


    In the "workflow" spreadsheet theres a combobox to pick the department(DEPARTAMENTO), and it works flawlessly going each by each, but i added an option called "todos os departamentos" which means "all departments" that could automatize everything so i wouldnt need to send department after department. the filter works by the column "A" in the "APOIO" spreadsheet.


    what i need is the help to create the code or the code(lol) to do it, sort of like creating a loop going through every department and making it ready to send to the department's people.


    thank you so much already and sorry for any grammar/english mistakes lol im learning a lot of things at the same time and trying to do my best!!

  • Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your WORKFLOW sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. First enter the data in B5 and B6 and then make a selection in B4. If you select TODOS OS DEPARTAMENTOS the emails for all departments will be created automatically. If you select only one department, the email for that department will be created automatically. You don't need any of the other macros and you don't need the Gerar FUP button.

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • My apologies. Please use this revised version:

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • My apologies. Please use this revised version:

    Heyyy man, thank you so much already!! i have a few questions, if i may?


    First of all, the code works but it only captures the first department (account & taxation) correctly, all the other worksheets it creates has distorted columns(by that i mean the wrong columns deleted/left there) and blank data... you know what happened?


    thank you so much!!!!

  • In the sample file you posted, the Planilha Completa sheet had no data so it was hard to test the macro. Can you post a copy of your file that has some data in that sheet to make testing easier?

    You can say "THANK YOU" for help received by clicking the :thumbup: 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:

    You can say "THANK YOU" for help received by clicking the :thumbup: 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:

    Hey...

    Now it worked with the first one, as it did before (accounting & taxation), but the rest of the departments dont even start :(

  • I tested the macro and it works properly. The macro first checks if a department exists in column F of sheet Planilha Completa. If a particular department does not exist, then then macro doesn't create a new workbook or email. I assumed that this is the way you want it to work. Did I assume correctly? Also, you have to make sure that the department names in column F of sheet Planilha Completa are exactly the same as those in column A of the Apoio sheet.

    You can say "THANK YOU" for help received by clicking the :thumbup: 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!