Save As an Excel file with a file name that includes yesterday's date and data from a cell in another sheet

Question

I have a macro that saves an Excel file with file name that includes the previous day date. It works fine, but I also want to include in the file name a text that exists in another sheet (sheet name is DSP) and in cell B8

This is the code I have:

ActiveWorkbook.SaveAs Filename:= _
        "\\mypath\" & _
    "Azal PHR " & Format(Date - 1, "mm-dd-yyyy")

I have found my mistake. the sheet is in another file called PHR Automatic in the same path, so How to include it now? — hussam, Jul 28 '23 at 13:16
Related: https://stackoverflow.com/questions/8047943/excel-vba-getting-range-from-an-inactive-sheet — DecimalTurn, Jul 29 '23 at 07:37

score 0 · Answer 1 · answered Jul 28 '23 at 13:30

Just need to concatenate the workbook and sheet reference to the end. Note you may need to change the workbook name depending on the file extension (e.g. if it has macros as well it will be .xlsm)

ActiveWorkbook.SaveAs Filename:= "\\mypath\" & "Azal PHR " & _ 
Format(Date - 1, "mm-dd-yyyy") & _ 
Excel.Workbooks("PHR Automatic.xlsx").Worksheets("DSP").Range("B8").Value

Save As an Excel file with a file name that includes yesterday's date and data from a cell in another sheet

1 Answers1