This Excel VBA Macro example explains how to create basic chart using simple VBA Code.
You can use Chart Object to add chart on specific sheet and use data from specific range.