Basic Excel Macro to create graphs

[WannabePrgmr]

I have a simple Excel Spreadsheet that continuously updates with data (both columns and rows). I am trying to build a macro that will start with the first row (which is actually row 2) and graph all the data in that row until an empty cell is found and stop. Then I need it to loop to the next row and graph that row until an empty cell found. I need it to continue row by row until and empty cell is found in Column A. I'd like each of the graphs to be on a seperate tab.

I've tried to use suggestions online and plug and play with different code and have only created a mess. Any help on how to set this up would be greatly appreciated!

Thanks.

[ADezii]

The following Code will dynamically create a 3D Clustered Column Chart on Sheet1 based on Data contained in Cells starting from Row2/Column A. The Data will consist of all Columns in Row 2 whose Cells are not Empty. No Validation is performed on the Values within these Cells.

Dim intCol As Integer
Dim intRow As Integer
Dim rng As Excel.Range

intRow = 2
intCol = 1

Do Until Cells(intRow, intCol).Value = ""
  intCol = intCol + 1
Loop

Set rng = Range("A" & CStr(intRow) & ":" & Chr$(intCol + 63) & CStr(intRow))
    rng.Select

Charts.Add

ActiveChart.ChartType = xl3DColumnClustered
ActiveChart.SetSourceData Source:=rng, PlotBy:=xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

With ActiveChart
  .HasTitle = False
  .Axes(xlCategory).HasTitle = False
  .Axes(xlSeries).HasTitle = False
  .Axes(xlValue).HasTitle = False
End With

This should be a good starting point for you.

[WannabePrgmr]

Thank you for the great start! This worked exactly like you said it would, however after the chart was created, a run-time error came up stating "The specified dimension is not valid for the current chart type". When I click "Debug", it highlights:

ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

[WannabePrgmr]

The following Code will dynamically create a 3D Clustered Column Chart on Sheet1 based on Data contained in Cells starting from Row2/Column A. The Data will consist of all Columns in Row 2 whose Cells are not Empty. No Validation is performed on the Values within these Cells.

Dim intCol As Integer
Dim intRow As Integer
Dim rng As Excel.Range

intRow = 2
intCol = 1

Do Until Cells(intRow, intCol).Value = ""
  intCol = intCol + 1
Loop

Set rng = Range("A" & CStr(intRow) & ":" & Chr$(intCol + 63) & CStr(intRow))
    rng.Select

Charts.Add

ActiveChart.ChartType = xl3DColumnClustered
ActiveChart.SetSourceData Source:=rng, PlotBy:=xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

With ActiveChart
  .HasTitle = False
  .Axes(xlCategory).HasTitle = False
  .Axes(xlSeries).HasTitle = False
  .Axes(xlValue).HasTitle = False
End With

This should be a good starting point for you.

I apologize. I didn't know if I should've replied here or post new, so I did both.

Thank you for the great start! This worked exactly like you said it would, however after the chart was created, a run-time error came up stating "The specified dimension is not valid for the current chart type". When I click "Debug", it highlights:

ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

Thanks!

[ADezii]

Try changing the offending Line of Code to:

ActiveChart.Location Where:=xlLocationAsNewSheet

[WannabePrgmr]

Try changing the offending Line of Code to:

ActiveChart.Location Where:=xlLocationAsNewSheet

Perfect! Thanks for your help! After messing around for an hour or two, I figured out the rest of what I needed for this as well! Thanks for getting me started and following up!

[ADezii]

Your are quite welcome. I guess you are no longer a WannebePrgmr but a FullFledgedPrgm r (LOL).