Logic Help

HI,

Please help me with a logic, to achieve the below.

I have a xls with multiple sheets, In each sheet I am calling macros for loading the data. In Load Stats1 tab in C2 cell we have a reference number, this reference number has to be populated in each .bat file I am generating.

Below is the script I am using to run on Loadstats1 and Loadstats3 worksheets, but the value of Loadstats1.c1 vell value is not coming properly.

Public Sub create_batch_file()
     
    Const MY_FILENAME = "C:\temp\test.bat"
     
    Dim FileNumber As Integer
    Dim retVal As Variant
     
    FileNumber = FreeFile
     
     'creat batch file
    Open MY_FILENAME For Output As #FileNumber
    'create file name using reference number
    Print #FileNumber, "run_daily_load.bat > Log_'Load Stats3'!C1"; .Log ' I am getting the error here
    Print #FileNumber, "exit"
    Close #FileNumber
     
End Sub

Please help me how can I get the value and use it in all worksheets for populating it in my script.

Regards,
Shruthi