VBA Code Tweaking Needed for Graph References

[lorber123]

In the attached, I have VBA coding that duplicates the Main Sheet for each department found on the data tab. When the Main Sheet is copied for each department, the graphs seen in row 101 and below have a horizontal axis reference to the Main Sheet versus the current department sheet. Is there something in the VBA code or the horizontal access formula on the graphs that we can change so that each worksheet that gets created can reference its own worksheet for the graphs?

I have attached the worksheet.

[FerJo]

Easy one :P

Please Login or Register  to view this content.

Supposing that the names you want to appear in the graphs are in that range...

A better/simpler solution would be to use a sheet-level named range for the cells that you want to reference... ie, a named range for 'Main Sheet' (not for the book!) called "xNames" = $AC$3:$AC$100
Thus, this sheet-level named range would get copied to the rest of the sheets when duplicating it:

Please Login or Register  to view this content.

so you don't have to worry about changing the range address in the code if you ever modify the template sheet.

Hope it helps!