Calling module inside another module in VBA Excel

Question

I am having some problems to import one module inside another in my VBA code. Basically, I have a work order spreadsheet where I have a macro button in the range of options that I have customized in excel to create a copy (duplicate) the spreadsheet that I use as a base to create the others. Therefore, I have several service order sheets for each client.

In this duplicate macro, I’m trying to add another module after duplicate it, to clear the content of the base service order (called "OS"). So, for example, I have the OS spreadsheet which is my base spreadsheet, I put in it the services and information that I will provide to the FULANO client, I click on the macro button to duplicate, it creates a copy of the spreadsheet and automatically names the client name (which is in cell B10) and his car (B15). Then I wanted you to clean the contents of the base OS automatically (until then, I was doing it manually, creating the copy and then going there to clean the base OS). However, in the code I did, he stopped creating the copy, and is only cleaning the contents of the OS spreadsheet.

This is my current code:

Option Explicit

Sub NewClientOS()
    'criar ordem de serviço do cliente
    Dim sName As String
    Dim sCar As String
    Dim sTemp As String
    Dim ws As Worksheet: Set ws = Sheets("OS")
    
        sName = ws.[B10].Value
        sCar = ws.[B15].Value
        sTemp = sName & " " & sCar
        Sheets("OS").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = sTemp
        
    clearContent

End Sub
 
Sub clearContent()
    'limpar conteúdo da planilha
     
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("OS")

    ws.Range("rClient").Select
    Selection.ClearContents
    ws.Range("rWorks").Select
    Selection.ClearContents
    ws.Range("rObs").Select
    Selection.ClearContents
    ws.Range("rDescount").Select
    Selection.ClearContents
 
    ws.Range("B10:G10").Select
    
End Sub

If anyone can and can help me solve this problem, I would be most grateful.

Answer 1

Sub NewClientOS()
'criar ordem de serviço do cliente
Dim sName As String
Dim sCar As String
Dim sTemp As String
Dim ws As Worksheet: Set ws = Sheets("OS")

    sName = ws.Range("B10").Value
    sCar = ws.Range("B15").Value
    sTemp = sName & " " & sCar
    Sheets("OS").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = sTemp
    
    Call clearContent

 End Sub

 Sub clearContent()
'limpar conteúdo da planilha
 
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("OS")
ws.Select
ws.Range("rClient").ClearContents
ws.Range("rWorks").ClearContents
ws.Range("rObs").ClearContents
ws.Range("rDescount").ClearContents
ws.Range("B10:G10").Select

End Sub

Answer 2

Hello!

When I separated into different modules, this problem did not occur.

In this case, what I did was right-click on Planilha1(OS)>Insert>Module (image below) and, in this module created, I inserted the module "Clear Contents" and it worked.

However, if it is the case to delete the contents of the "OS" tab, instead of creating separate modules, I would leave the code to delete the contents in the same Sub (I at least prefer to do so).

Any problems, let me know.