none
Macros realize that the contents of EXCEL cells change every time they are printed RRS feed

  • Question

  • A certain document of a friend is made by EXCEL, one document per day. It is too troublesome to modify the date every time. Therefore, the date of the table can be increased in order to achieve the number of times of input and printing.
    Thanks in advance for your prompt attention. 
    Thursday, December 27, 2018 4:26 AM

Answers

  • Hi,

    You can define in the Page Setup-->Header/ Footer--> Custom Header-->Left Section (For Example)

    Printed On: &[Date] &[Time]

    Every time you will print you will have the Date & Time

    Or you can replace the print button with "your" button for certain task


    Guy Zommer

    • Marked as answer by Nicholasmin Friday, December 28, 2018 9:15 AM
    Thursday, December 27, 2018 5:28 AM
  • Hi Nicholasmin,

    Please follow the steps below:

     1. ALT+F11 opens EXCEL's VBA window

     2. Select menu insertion in the VBA window, then select the menu item module.

     3. Enter the following code

    Sub PrintDate() 
      dim n as integer 
      n=inputbox("Please enter the number of prints") *1 
      for i=1 to n 
        activesheet.PrintOut Copies:=1 
        [J2]= application.text(i,"1") & "day" 
      next 
    End Sub

     4. Close the macro window and save the EXCEL file as a macro-enabled workbook (*.xlsm).

     5. In the View tab, in the Macros group, click the Macros button.

     6. In the pop-up execution macro window, select PrintDate, click the Execute button, enter the number of prints in the pop-up dialog box, click OK.

    Hopefully it helps you.

    Best Regards,

    Lina



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    • Edited by Lina-MSFT Thursday, December 27, 2018 6:12 AM
    • Marked as answer by Nicholasmin Friday, December 28, 2018 9:15 AM
    Thursday, December 27, 2018 5:36 AM

All replies

  • Hi,

    You can define in the Page Setup-->Header/ Footer--> Custom Header-->Left Section (For Example)

    Printed On: &[Date] &[Time]

    Every time you will print you will have the Date & Time

    Or you can replace the print button with "your" button for certain task


    Guy Zommer

    • Marked as answer by Nicholasmin Friday, December 28, 2018 9:15 AM
    Thursday, December 27, 2018 5:28 AM
  • Hi Nicholasmin,

    Please follow the steps below:

     1. ALT+F11 opens EXCEL's VBA window

     2. Select menu insertion in the VBA window, then select the menu item module.

     3. Enter the following code

    Sub PrintDate() 
      dim n as integer 
      n=inputbox("Please enter the number of prints") *1 
      for i=1 to n 
        activesheet.PrintOut Copies:=1 
        [J2]= application.text(i,"1") & "day" 
      next 
    End Sub

     4. Close the macro window and save the EXCEL file as a macro-enabled workbook (*.xlsm).

     5. In the View tab, in the Macros group, click the Macros button.

     6. In the pop-up execution macro window, select PrintDate, click the Execute button, enter the number of prints in the pop-up dialog box, click OK.

    Hopefully it helps you.

    Best Regards,

    Lina



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    • Edited by Lina-MSFT Thursday, December 27, 2018 6:12 AM
    • Marked as answer by Nicholasmin Friday, December 28, 2018 9:15 AM
    Thursday, December 27, 2018 5:36 AM