VBA Code To Use Macro.A In Workbook.A On Workbook.B.WorkSheet1 Without 'Opening' It-CodePudding

Link To Files

The First File ConvertToMacroEnabledWorkbook.xlsm because initially I though is was because the file originally come as .xls and not macro enabled so I found a script that converts them to xlsm files. This file also contains the Macro to format the second file DailyReport_Canada.xlsm. is=t doesn't have the macro, but I need to excute the macro from ConvertToMacroEnabledWorkbook.xlsm onto DailyReport_Canada.xlsm.

The Second file is the file to formal as stated in the first paragraph.

I have a Macro.A that applies formatting and I want to be able to apply this formatting to a predefined workbook that contains no macros so I need to execute Macro.A in Workbook.A on Workbook.B

An help is greatful. I have have dabbled in VBA for a while and usually find a solution, but this one I have had for the first time ask for help.

Thank You in Advance

Linked Code To Button Workbook.A:

Sub Open_External_Workbook()

Dim FormatMyInvoice As Workbook

Set FormatMyInvoice = Workbooks.Open("C:\Scatchpad\FTL\Excel\_RigDailyReport_Canada.xlsm")

Application.Run "'" & FormatMyInvoice & "'!FormatCostSheet"

End Sub

Macro.A Inside Workbook.A

Sub FormatCostSheet()
'
' FormatCostSheet Macro
'

'
    Rows("3:3").Select
    ActiveWindow.SmallScroll Down:=9
    Rows("3:71").Select
    Selection.RowHeight = 32
    Range("Y36:Z37").Select
    With Selection.Font
        .Name = "Arial"
        .Size = 36
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("T8:T35").Select
    Selection.Style = "Currency"
    Range("R9:R35").Select
    Selection.Style = "Currency"
    ActiveWindow.SmallScroll Down:=-9
    With Selection.Font
        .Name = "Arial"
        .Size = 22
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("T9:T35").Select
    With Selection.Font
        .Name = "Arial"
        .Size = 22
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
End Sub

CodePudding user response：

Updated after checking the files

There is no need to use Application.Run to run a code from the same workbook. Just Call the Sub you need, just don't forget to activate the target workbook that needs be formatted first.

Sub Open_External_Workbook()
  Dim FormatMyInvoice As Workbook
  Set FormatMyInvoice = Workbooks.Open("Z:\OneDrive-2022-07-03\DailyReport_Canada.xls")
  FormatMyInvoice.Activate
  Call FormatCostSheet
End Sub