Changing file name and dates VBA macro

  • I have a macro that just copies and pastes from a sheet into a destination sheet. The data sheet changes (we get a report each week) but the destination sheet is always the same. the two sheets can be saved in the same working folder.

    the format of the name of the excel sheet that is changing is XXX version_XXX XXXXXX XXXXX FY2022 XXXX WE 11-10-2021. The date on this file name is always the previous Monday which is when the report is run.

    • I am thinking of just using a text box, to input the new sheet name (data sheet). Is this the best way to do this or is there a more automated way. -considering the date is always the previous month is there a way to just get the previous month and input into the macro? note the date format below.
    • Is there a way for the copy, paste to occur on the same sheet, ie just copy paste over old data, instead of creating a new sheet as the macro below does.

    The below is my current starting macro. This is what i have been able to do using record macro.

    The above is repeated 3 times for the three separate lines to copy and paste.(A1, A2, A3). So the second copy paste takes the same but a different manager and the third copy paste takes the total of both the first and second manager.

    Ideas also for robustness in programming would also be appreciated, as this has to be run by other employees.

  • Hi royUK I hope your well!

    Thank you for your response. I cant post an example workbook due to privacy.

    I am going to post what i found from stack overflow below.

    The file name does come up as the previous monday so the code the below does need some improvement.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!