VBA | Calling Same fuction on many worksheets-CodePudding

Let me start by saying that I am new to VBA. So, probably my question is something really simple that I couldn't find. I am working on an excel file that is a calendar with some specifications. I have a code that will send emails, according to what is selected on the calendar. The code I have works. When I click the command button on the sheet that corresponds to January, the emails are prepared. The problem is with the other worksheets. I don't think it makes sense to repeat the code on the other sheets, but I don't know how to call it from there.

This is part of the code I have on the January sheet.

Private Sub CommandButton1_Click()
    Call sendEmail_testemunho("Semana1")
End Sub


Public Sub sendEmail_testemunho(nSemana As String)

On Error GoTo ErrHandler
    
    Dim testemunho, hcell, cell, mcell, rgHorarios, diasSemana As Range
    Set testemunho = Range(nSemana)
    Set diasSemana = Range("DiasSemana")
    
    Set emails = Worksheets("Publicadores Locais Horários").ListObjects("emails").Range
   ...
   ...
   ...

Set testemunho = Nothing


ErrHandler:
    Debug.Print Err.Message & " on line " & Erl    
End Sub

I tried to call the sub on another sheet but it just sends this error.

I also tried to just coppy and past the first sheet, but then when I click the button I have another error.

I think I should have put the code somewhere else, but I don't know where.

Do you have any suggestions?

CodePudding user response：

You have to use the codename as prefix

Code in January-sheet: codename = wsJanuary

Public sub sendMail(Email as string)
  msgbox email
End sub

Code in Febuary-sheet: codename = wsFebuary

Public sub sendMail(Email as string)
  wsJanuary.sendMail Email
End sub

BUT the better solution would be to put sendMail into a normal module. Then you can access it without prefix.