0

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
Bruno Brottes
  • 3
  • 2

1 Answers1

0

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
ENIAC
  • 813
  • 1
  • 8
  • 19
  • It's that What I have already? And it doesn't work. It open the other workbook and should apply the macro yes, but getting a 400 error – Bruno Brottes Jul 03 '22 at 03:38
  • Currently, I see `Application.Run "'" & FormatMyInvoice & "'!FormatCostSheet"` in your code and `FormatMyInvoice` is the new workbook you open but it doesn't contain any macros. In other words, to run a macro via `Application.Run`, you need to specify the name of the workbook that contains it. – ENIAC Jul 03 '22 at 05:13
  • The Macro is not in the target workbook – Bruno Brottes Jul 03 '22 at 05:56
  • Now it looks like `FormatCostSheet` lives in `_RigDailyReport_Canada.xlsm` workbook (which is `Workbook.B` as far as I understand), but you wrote that `Open_External_Workbook` and `FormatCostSheet` are located in `Workbook.A` – ENIAC Jul 03 '22 at 06:09
  • @BrunoBrottes, can you provide those workbooks? – ENIAC Jul 03 '22 at 06:10
  • See post, files are linked at the top of the post to Microsoft SkyDrive – Bruno Brottes Jul 03 '22 at 12:09
  • @BrunoBrottes, see my updated answer. – ENIAC Jul 03 '22 at 13:58
  • Thats Works...thanks You. Working on my next problem... – Bruno Brottes Jul 03 '22 at 14:41
  • Here I posted the completed version https://stackoverflow.com/questions/72849292/how-i-made-a-workbook-that-executes-a-macro-onto-another-workbook-and-mines-some – Bruno Brottes Jul 03 '22 at 19:22
  • FWIW, use of the `Call` keyword is not required. [See this](https://stackoverflow.com/a/56874163) – chris neilsen Jul 03 '22 at 20:39