Optimizing my VBA code with a loop

jalrs

Active Member
Joined
Apr 6, 2022
Messages
300
Office Version
  1. 365
Platform
  1. Windows
Hello guys,

So on the last weeks I managed to optimize my current code with the help of Kevin9999, Mumps and Joe4. Credits to them. Currently my situation is that the code that I managed to get takes 7 clicks for each department to trigger the last action. My boss thinks this is too much, so I need help optimizing it.

1st action is regarding copying the filtered data and pasting into respective template according to the criteria.
2nd action is to save the template with a specific name, so the file can next follow as attachment with the correct name, avoiding losing the template and having to create a new one from the start.
3rd action is to send the e-mails.

I would like to know if it's possible to complete all actions through a loop, so instead of having 7 clicks for each action for each department, 3 clicks would make it, since I'm pretending to loop.

The dropbox files I'm uploading don't have any lines of code, since I created a brand new bogus information folder to share with whoever may help me.
Next you can find the code for whole process of Apoio SP department, as of now, as an example:

1st action:
VBA Code:
Sub filtroApoioSP()

    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
   
    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks("T_ApoioSP.xlsm")
   
    Set ws1 = wb1.Worksheets("Stock Trânsito")
    Set ws2 = wb2.Worksheets("Pendentes")
   
    ws2.UsedRange.Offset(1).ClearContents
   
    Dim lr1 As Long, lr2 As Long
   
    lr1 = ws1.Cells(Rows.Count, 1).End(3).Row
    lr2 = ws2.Cells(Rows.Count, 1).End(3).Row + 1
   
    With ws1.Range("A5:AV" & lr1)
   
        .AutoFilter 46, "Apoio SP"
        .AutoFilter 47, "Em tratamento"
        .Offset(1).Copy ws2.Cells(lr2, 1)
       
        With ws1.Range("BH6:BH" & lr1)
       
           .Copy ws2.Cells(2, 49)
          
        End With
       
        .AutoFilter
       
    End With
   
    lr2 = ws2.Cells.Find("*", , xlFormulas, , 1, 2).Row + 1
    ws2.Range("A" & lr2 & ":A1001").EntireRow.Delete
   
    wb2.Activate
   
    ws2.Activate
   
    Dim lr3 As Long
   
    lr3 = Cells(Rows.Count, "AT").End(xlUp).Row
   
    If lr3 > 1 Then
   
        Range("AY2:AY" & lr3).FormulaR1C1 = _
            "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],TAB_FDB!C[-50]:C[-49],2,0))"
           
    End If
       
End Sub

2nd action:
VBA Code:
Sub copyPTApoioSP()

Dim path As String
Dim filename As String

path = "C:\Users\joafrodrigue\Desktop\prototipo\Difusao\"
filename = "ST_até31032022_Apoio SP"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:=path & filename & ".xlsx", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True

End Sub

3rd action:
VBA Code:
Sub mailptApoioSP()

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "<BODY style = font-size:12pt; font-familt:Arial>" & _
"Olá equipa, <br><br> Envio este e-mail com os pendentes em questão, até à data, em anexo.<br><br>" & _
"Cumprimentos,<br>João Rodrigues"

On Error Resume Next
    With OutMail
        .To = Cells(2, 11).Value
        .CC = Cells(2, 12).Value
        .BCC = Cells(2, 13).Value
        .Subject = Cells(2, 14).Value
        .Display
        .HTMLBody = strbody & .HTMLBody
        .Attachments.Add "C:\Users\joafrodrigue\Desktop\prototipo\Difusao\ST_até31032022_Apoio SP.xlsx"
       
    End With
    On Error GoTo 0

Set OutMail = Nothing

End Sub

Here is the share folder file share
There should be two folders inside main folder called "attachments" and "finaldocname"

I think the "Instructions" Sheet is clear on what I pretend, assuming it is possible.
Macro sheets (1,2,3,4) are there because on the instructions, the source columns to copy are different within each macro as you can see on instruction sheet. All of these are located inside Stock.xlsm

Any additional information regarding the "AS-IS" code just ask.
I hope my "TO-BE" is possible.

Thanks for your attention!
 
Last edited:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Up.

Perhaps @mumps could you provide some assistance since you talked about one button doing it all? I think it is consistent now, made some changes here and there to the layout.

Sorry for the tag, hope to not be violating any rule. Felt like doing it before crossposting.

Any help is greatly appreciated.

Thanks
 
Upvote 0
Up.

@kevin9999 perhaps you could help me on this, since you helped me optimizing most of first action code, except for that r1c1 formula?

Thanks
 
Upvote 0
@Joe4 I see there was a comment from my side deleted for hijacking reasons. Since you are admin, my sincere apologies, that wasn't my intention. I don't know if it was you or not, can't know on my side, but you could see it on the logs and address my apologies to whoever it may concern. I read the rules, I'm aware of the rules, but there was no rule regarding what I did, even tho my intention wasn't to hijack other member's post, or I might misunderstood it.

Additionally, I would like to politely ask you for some assistance here since I haven't succeed yet and I'm kind of desesperate to finish this project. I wish I could tell the fault was mine, but it ain't, and I hate to be playing the devil here, bothering people out in order to find help. So sorry to bother once again. Just desperate at this time.

Thanks for your time
 
Upvote 0
I do not know which Moderator did it, but it doesn't really matter. None of us take it personally, so no need to apologize to anyone directly.

Also, please be careful about tagging a lot of people, pleading for help. That is not really how this forum works - you do not seek out specific individuals for help -- those who feel they can help with a certain question are free to respond (or not), if they are willing and able. That sentiment is conveyed in rule 4 here: Message Board Rules. Though that rule mentions messaging via Conversations, the spirit/sentiment is the same.

In regards to your question, if your goal is to really just reduce the number of clicks to run everything, perhaps you want to look at calling one macro from another, so you don't need a separate click action to run each one. See: Run a Macro from a Macro - VBA Code Examples
 
Upvote 0
I do not know which Moderator did it, but it doesn't really matter. None of us take it personally, so no need to apologize to anyone directly.

Also, please be careful about tagging a lot of people, pleading for help. That is not really how this forum works - you do not seek out specific individuals for help -- those who feel they can help with a certain question are free to respond (or not), if they are willing and able. That sentiment is conveyed in rule 4 here: Message Board Rules. Though that rule mentions messaging via Conversations, the spirit/sentiment is the same.

In regards to your question, if your goal is to really just reduce the number of clicks to run everything, perhaps you want to look at calling one macro from another, so you don't need a separate click action to run each one. See: Run a Macro from a Macro - VBA Code Examples
Alright Joe, thanks for your reply! I did tag the people that helped me throughout the stages of the project that i wish to be completed already. I thought that would be ok to do. I understand the reasons for not being ok aswell!

I will read the link and comeback to you if needed.

Thanks for your time and patience!
 
Upvote 0
I did tag the people that helped me throughout the stages of the project that i wish to be completed already. I thought that would be ok to do.
OK, just be aware that just because someone once helped you with something, doesn't mean that they should be expected to help you with other questions (even if it is for the same project). For example, there are many things that are not in my wheelhouse that I am not comfortable helping out on (like Charts and Power Query, to name just a few).

I would say it would be OK to tag someone if it is a follow-up to something specific that they already helped you on (a follow-up to some specific code that they posted), for example, but you should probably avoid doing it otherwise.
 
Upvote 0
OK, just be aware that just because someone once helped you with something, doesn't mean that they should be expected to help you with other questions (even if it is for the same project). For example, there are many things that are not in my wheelhouse that I am not comfortable helping out on (like Charts and Power Query, to name just a few).

I would say it would be OK to tag someone if it is a follow-up to something specific that they already helped you on (a follow-up to some specific code that they posted), for example, but you should probably avoid doing it otherwise.
Yes Joe, thanks for your time, I will keep that in mind for the future.
 
Upvote 0
I do not know which Moderator did it, but it doesn't really matter. None of us take it personally, so no need to apologize to anyone directly.

Also, please be careful about tagging a lot of people, pleading for help. That is not really how this forum works - you do not seek out specific individuals for help -- those who feel they can help with a certain question are free to respond (or not), if they are willing and able. That sentiment is conveyed in rule 4 here: Message Board Rules. Though that rule mentions messaging via Conversations, the spirit/sentiment is the same.

In regards to your question, if your goal is to really just reduce the number of clicks to run everything, perhaps you want to look at calling one macro from another, so you don't need a separate click action to run each one. See: Run a Macro from a Macro - VBA Code Examples
Hey Joe,

It did the job. Marked as solution

Thanks for the recommendation!
 
Upvote 0
I do not know which Moderator did it, but it doesn't really matter. None of us take it personally, so no need to apologize to anyone directly.

Also, please be careful about tagging a lot of people, pleading for help. That is not really how this forum works - you do not seek out specific individuals for help -- those who feel they can help with a certain question are free to respond (or not), if they are willing and able. That sentiment is conveyed in rule 4 here: Message Board Rules. Though that rule mentions messaging via Conversations, the spirit/sentiment is the same.

In regards to your question, if your goal is to really just reduce the number of clicks to run everything, perhaps you want to look at calling one macro from another, so you don't need a separate click action to run each one. See: Run a Macro from a Macro - VBA Code Examples
Hello again Joe,

Unfortunately it only did the job for me, therefore I unmarked as solution, and wonder if you could provide me some help. My boss keeps insisting on the loop so I guess I need to do it.

When I say for me, it is because I'm doing an internship allocated to my school on a company. If I was a company worker, the call method would work, because maintenance of it would be on my side. Although, that's a cool feature to be aware of, and I won't forget it.

I already thought about the looping process, but couldn't find any helpful post/video about it.

Could you please assist me? I'd more than happy to tell you in detail what I need and hopefully we would sort this really quick.

Thanks Joe
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top