Code to generate File Name

I presently have code that copies information from one workbook sheet and paste it into a different workbook. Part of the code is:

Please Login or Register  to view this content.

The code works fine, each year I change the year and each month change the month so the information goes into the correct file and sheet month.

What I would like to do is develop code that would automatically generate the file name as it appears above and the sheet name so the information can then be pasted in the correct fie and sheet. With that in mine, I have added a sheet called TABLES and added the following information;

B1 text =C:\User\Fis\ACCT\AGENT\DAILY\
B2 text = DATA -
B3 formula =YEAR(TODAY()) which returns 2023
B4 formula =MONTH(TODAY()) which returns 10 but the cell is custom formatted to MMM (OCT)[HTML]

Hopefully, it can be done, especially getting the month correct as text (Jan, Feb etc) instead of (1, 2, 3) so it matches up with the text named sheets.

As always thanks for any help provded.

Check this:

Please Login or Register  to view this content.

I have attached a worksheet for reference. I have tried the suggested code provided above, but it failed to work. It stopped when evaluating the code for setting the file name.
Hopefully with the attached worksheet it can be adjusted so as to work as expected.

Thank you all for your assistance.