I presently have code that copies information from one workbook sheet and paste it into a different workbook. Part of the code is:
Set dataworkbook = Workbooks.Open("=C:\User\Fis\ACCT\AGT\DAILY\DATA - 2023.XLSM")
Set dataSheet = dataWorkbook.Sheets("JAN")
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.