How I Made A Workbook That Executes A Macro Onto Another Workbook And Mines Some Data And Other Stuff

Question

This code has two components to it.

A Workbook.A with a Macro.A which targets Workbook.B and run Macro.A upon it even when Workbook.B is not macro enabled.

The initial goal was to apply formatting to the spreadsheet for readability and focus on dialy costs.

This Workbook also

• Fetches data from Workbook.B and manipulates it and uses inside Workbook.A
• Creates a file name and directory path to save a PDF and sends to Default Printer
• This macro also overcome the merged cell problem with copy and paste inside VBA

Sub FormatCosts()

 Dim FormatMyInvoice As Workbook
 Dim WellName As String
 Dim TheDate As String
 Dim JobNumber As String
 Dim FileName As String
 Dim JobROOT As String
 Dim FTLROOT As String
   
 
 Application.ScreenUpdating = False
 Application.DisplayAlerts = False

 Set FormatMyInvoice = Workbooks.Open("C:\SAP_Path\FTL\Excel\_RigDailyReport_Canada.xls")

 FormatMyInvoice.Activate
 
 ThisWorkbook.Worksheets("Morning Report Sheet").Range("F16").Value = Workbooks("_RigDailyReport_Canada.xls").Worksheets("Daily Report").Range("Y36")
 
 
 ThisWorkbook.Worksheets("Morning Report Sheet").Range("F17").Value = Workbooks("_RigDailyReport_Canada.xls").Worksheets("Daily Report").Range("Y37")
 

 ThisWorkbook.Worksheets("Morning Report Sheet").Range("F18").Value = Workbooks("_RigDailyReport_Canada.xls").Worksheets("Daily Report").Range("H36")
 

 ThisWorkbook.Worksheets("Morning Report Sheet").Range("F19").Value = Workbooks("_RigDailyReport_Canada.xls").Worksheets("Daily Report").Range("D69")
 
 
 WellName = ThisWorkbook.Worksheets("Morning Report Sheet").Range("F9")
 TheDate = Format(Date, "mmmm dd yyyy")
 JobNumber = ThisWorkbook.Worksheets("Morning Report Sheet").Range("F11")
 FileName = TheDate & "_" & JobNumber & "_" & WellName & ".xls"
 JobROOT = ThisWorkbook.Path & "\" & "05 Daily Cost" & "\"
 FTLROOT = "C:\Schlumberger\FTL\Excel\"

 ThisWorkbook.Worksheets("Morning Report Sheet").Range("E97").Value = (JobROOT & FileName & "_unSigned.pdf")
 
 Call FormatCostSheet
 
 ActiveWorkbook.SaveAs (JobROOT & FileName)
 
 ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:=(JobROOT & FileName & "_unSigned")
 
 ActiveWorkbook.Sheets("Daily Report").PrintOut
 
 ActiveWorkbook.Close
 
 Application.DisplayAlerts = True
 
 Application.ScreenUpdating = True
 
 Set FormatMyInvoice = Nothing

End Sub

The Formatting Macro Looks Like This

Sub FormatCostSheet()
'
' FormatCostSheet Macro
'

'
    Rows("3:70").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("T9:T35,R9:R35").Select
    Selection.Style = "Currency"
    
    Range("T9:T35,R9:R35,Q9:Q35").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

If you have any suggesttion on how this could work better, I would like to hear your thoughts