Run Macros from two separate workbooks.

[Kevineamon]

Hi guys;
I've created 2 separate workbooks. A Device workbook and an admin book.
Both of these workbooks have macros attached. I've pasted one of the pieces of code below. The one for the other workbook is almost identical.

Problem is I don't want, to have to create two lists of sheets anymore.

As an example - let's say I want my new sheets, to have the prefix - "Test"

I want to click on the macro, up pops the dialogue box and I enter "Test."
Now it will go into both workbooks and make sheets, with that prefix.

I'm pretty good with excel, and can read VBA, but I'm not great with it.
I was thinking of designing a new macro, that will simply trigger the names of both of these macros at once. It will then give me two input boxes right?

Can anyone think of something better. Again I'm not great with VBA.

'---------------------------------------------------------------------------------------
' File   : NewDevice
' Author :
' Date   : 25/09/2018
' Purpose: Copies 3 devices sheets per the users input
'---------------------------------------------------------------------------------------
Option Explicit


Public objExcel As Excel.Application    'new excel workbook object
Public userIn As String
Private numOfDevices As Integer
Private i, o As Integer
Private ws(3) As Worksheet


'---------------------------------------------------------------------------------------
' Adds new device sheets
'---------------------------------------------------------------------------------------
Public Sub AddDevice()


    On Error GoTo AddDevice_Error


    userIn = InputBox("Enter the Name of the new board", "Add New Sheets")
    If userIn = "" Then Exit Sub
    Do Until Not (InStr(userIn, " ") > 0) And Not InStr(userIn, "-") > 0 And Not InStr(userIn, "/") > 0
        userIn = InputBox("Name must not have special characters.", "Add New Sheets")
        If userIn = "" Then Exit Sub
    Loop
    
    
    'MsgBox numOfDevices & " devices will be created."
    optimizeStart Application
    
    'add template sheets to array
    Set ws(1) = ThisWorkbook.Worksheets("Template_Device_Details")
    Set ws(2) = ThisWorkbook.Worksheets("Template_Breaker_Details")
    Set ws(3) = ThisWorkbook.Worksheets("Template_IO")
        
    'copy and paste each sheet
    Application.DisplayAlerts = False
    
    For i = 1 To 3
        ws(i).Visible = xlSheetVisible
    Next i
    
    For i = 1 To 3
        ws(i).Copy after:=ThisWorkbook.Sheets(Sheets.Count)
    Next i
    
    For i = 1 To 3
        ws(i).Visible = xlSheetHidden
    Next i
    
    ThisWorkbook.Worksheets(Sheets.Count - 2).Name = userIn & "_Device_Details"
    ThisWorkbook.Worksheets(Sheets.Count - 1).Name = userIn & "_Breaker_Details"
    ThisWorkbook.Worksheets(Sheets.Count).Name = userIn & "_IO"
    
    
    'ThisWorkbook.Worksheets(Sheets.Count - 3).Range("Q7").value = userIn
    ThisWorkbook.Worksheets(Sheets.Count - 2).Range("P2").value = userIn
    ThisWorkbook.Worksheets(Sheets.Count - 1).Range("p7").value = userIn
    ThisWorkbook.Worksheets(Sheets.Count).Range("q3").value = userIn
    
    
    
    'release memory
    For i = 1 To 3
        Set ws(i) = Nothing
    Next i
                   
    ListSheets1
    
    optimizeEnd Application
        
        On Error GoTo 0
    Exit Sub
    
    


AddDevice_Error:
    Application.ScreenUpdating = True
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure AddDevice of Sub NewDevice"


End Sub

 

[DanteAmor]

In a module:

Sub Run_Macros()
    Dim UserIn As String
    UserIn = InputBox("Enter the Name of the new board", "Add New Sheets")
    If UserIn = "" Then Exit Sub
    Do Until Not (InStr(UserIn, " ") > 0) And Not InStr(UserIn, "-") > 0 And Not InStr(UserIn, "/") > 0
        UserIn = InputBox("Name must not have special characters.", "Add New Sheets")
        If UserIn = "" Then Exit Sub
    Loop
    '
    Run "'device.xlsm'!AddDevice", False, UserIn
    Run "'admin.xlsm'!AddDevice", False, UserIn
End Sub

In each of your books "device" and "admin":

'---------------------------------------------------------------------------------------
' File   : NewDevice
' Author :
' Date   : 25/09/2018
' Purpose: Copies 3 devices sheets per the users input
'---------------------------------------------------------------------------------------
Option Explicit


Public objExcel As Excel.Application    'new excel workbook object
Public UserIn As String
Private numOfDevices As Integer
Private i, o As Integer
Private ws(3) As Worksheet


'---------------------------------------------------------------------------------------
' Adds new device sheets
'---------------------------------------------------------------------------------------
Public Sub AddDevice(question, UserIn)


    On Error GoTo AddDevice_Error
    If question = True Then
        UserIn = InputBox("Enter the Name of the new board", "Add New Sheets")
        If UserIn = "" Then Exit Sub
        Do Until Not (InStr(UserIn, " ") > 0) And Not InStr(UserIn, "-") > 0 And Not InStr(UserIn, "/") > 0
            UserIn = InputBox("Name must not have special characters.", "Add New Sheets")
            If UserIn = "" Then Exit Sub
        Loop
    End If
    'MsgBox numOfDevices & " devices will be created."
    optimizeStart Application
    
    Dim pref As String
    Dim ws As Worksheet
    Dim hojas(), celdas()
    
    'add template sheets to array
    pref = "Template"
    hojas = Array("_Device_Details", "_Breaker_Details", "_IO")
    celdas = Array("P2", "P7", "Q3")
        
    'copy and paste each sheet
    Application.DisplayAlerts = False
    
    For i = 0 To UBound(hojas)
        Set ws = ThisWorkbook.Worksheets(pref & hojas(i))
        ws.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        ThisWorkbook.Worksheets(Sheets.Count).Visible = xlSheetVisible
        ThisWorkbook.Worksheets(Sheets.Count).Name = UserIn & hojas(i)
        ThisWorkbook.Worksheets(Sheets.Count).Range(celdas(i)).Value = UserIn
    Next i
    
    ListSheets1
    optimizeEnd Application
    On Error GoTo 0
    Exit Sub


AddDevice_Error:
    Application.ScreenUpdating = True
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure AddDevice of Sub NewDevice"
End Sub

--
Run macro: "Run_Macos" and the 3 sheets will be created in the 2 books

If you want to execute only the creation of sheets in a single book, then you have to create a macro like this:

    Run "'admin.xlsm'!AddDevice", True, ""