Charting Data Based on the active cell

My macro creates a new worksheet report with graphs and data. I want to be able to attach a macro that allows the chart data to change based on the active cell. My problem is that the macro needs to be located (I think) in the code module for the new worksheet. How do I create a macro in the code module for the new sheet using a macro? I thought someone asked a similar question before but I couldn't locate it. Thanks!!!