Dynamic chart title as part of a macro to be run in several worksheets

I have developed a dynamic chart title by binding words together in my worksheet and referring to the cell containing the title. eg. =Sheet1$L$2. This works fine. However the title is part of a recorded macro to be run in several workbooks and the sheet names vary and is never Sheet1 or Sheet2. Is there a way to replace Sheet1 above with a function that would refer to the sheet the graph data was obtained from?

I hope this makes sense I am a newbie.

Cross Posted at Ozgrid