Excel VBA Macro: create a chart from CSV?

Question

I have a CSV file with three columns (A,B,C).

I can record a Macro which selects Col A + Col B, then inserts a chart of A versus B.

This works, but the code generated contains a hardcoded ref to the 'Sheet1' like this:

...
ActiveChart.SetSourceData Source:=Range( _
        "'Sheet1'!$A:$A,'Sheet1'!$B:$B,'Sheet1'!$A:$A,'Sheet1'!$B:$B")
...

So I change this to match the active document like this:

...
ActiveChart.SetSourceData Source:=ActiveSheet.Range("$A:$B")
...

This works, however I need to also insert a chart using COL A + COL C, the generated code looks like this:

...
ActiveChart.SetSourceData Source:=ActiveSheet.Range("'Sheet1'!$A:$A,'Sheet1'!$C:$C")
...

How to a similarily change this code to make it sheetname-agnostic ? [ The issue is that how to do I select two columns which are not adjacent to each other - I think I was luck in the first example - its a special case ]

Answer 1

How about:

ActiveChart.SetSourceData Source:=ActiveSheet.Range("$A:$A,$C:$C")