Prompt user to enter cell value to save file name (with cell value)

As you want the procedure to be executed when the workbook is opened, you need to store the routine in the "ThisWorkbook" module.

The code I wrote was untested, please change the following (as well as the Sheet name you want to store the name in, e.g. Sheet1)

Worksheet("Sheet1").Range("A1").Value = strName

to

Worksheets("Sheet1").Range("A1").Value = strName

Hopefully it should be working!