Solved: create chart in seperated worksheet

[maraippo]

Hello..

I want to make a macro to create a chart from a table in different sheet. What i try to do is copy the information into a new sheet. Next i use the information copied to make a column clustered chart. The problem is the chart does not appead in the sheet it created.

Say, i copy the info from sheet3 to "chart1" sheet. Then i want to place the chart created to "chart1", but what happen is the chart appear in the 1st sheet of the workbook...

This is the code i've taken and modified:
[vba]
Sub Chart()
Dim work_book As Workbook
Dim last_sheet As Worksheet
Dim new_sheet As Worksheet


' Copy information from table

Sheets("sheet3").Select
Range("C4:J4,C6:J6").Select
Selection.Copy

' Make a new worksheet.
With ThisWorkbook
Set work_book = Application.ActiveWorkbook
Set last_sheet = _
work_book.Sheets(work_book.Sheets.Count)
Set work_book = Application.ActiveWorkbook
Set new_sheet = work_book.Sheets.Add(after:=last_sheet)
new_sheet.name = "newchart"
ActiveSheet.Paste

End With

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("newchart#").Range("A1:H1,A2:H2")
ActiveChart.Location Where:=xlLocationAsObject, name:="newchart#"
End Sub[/vba]

Can anyone help me to change it so that i can make the chart for every row in the specified table-each in different sheet named watever.

Total number of row is 36, means that there will be another 36 sheets added in the workbook

I hope the chart will appear in the same sheet i copy the range like above.

Thank you in advance

[Bob Phillips]

This worked with a couple of tweaks for me

[vba]

Sub Chart()
Dim work_book As Workbook
Dim last_sheet As Worksheet
Dim new_sheet As Worksheet


' Copy information from table

Sheets("sheet3").Select
Range("C4:J4,C6:J6").Select
Selection.Copy

' Make a new worksheet.
Set work_book = Application.ActiveWorkbook
Set last_sheet = _
work_book.Sheets(work_book.Sheets.Count)
Set work_book = Application.ActiveWorkbook
Set new_sheet = work_book.Sheets.Add(after:=last_sheet)
new_sheet.Name = "newchart"
ActiveSheet.Paste

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("newchart").Range("A1:H1,A2:H2")
ActiveChart.Location Where:=xlLocationAsObject, Name:="newchart"
End Sub
[/vba]

[maraippo]

hmm..somehow the chart still moves to the first sheet (sheet1)...

i need the chart to be placed in the same sheet i use the range from.

or maybe its the setting in my excel :-/

should be no problem rite? since code already state name of the sheet the chart should be.

[maraippo]

i tried to copy it to another workbook and it suddenly work. hmm...dont know what happened with the last workbook

by the way..do you have idea on how to make it able to make chart for every row in the table?

here is the sample of the file...

[Bob Phillips]

[vba]

Sub makechart()
Dim work_book As Workbook
Dim last_sheet As Worksheet
Dim new_sheet As Worksheet
Dim dataSheet As Worksheet
Dim LastRow As Long
Dim i As Long

' Copy range to make chart

Application.ScreenUpdating = False

Set work_book = Application.ActiveWorkbook
Set dataSheet = Worksheets("Sheet3")

LastRow = dataSheet.Cells(dataSheet.Rows.Count, "B").End(xlUp).Row - 2
For i = 6 To LastRow

dataSheet.Select
Union(dataSheet.Range("C4:J4"), dataSheet.Cells(i, "C").Resize(, 8)).Copy

' Make a new worksheet.
Set last_sheet = work_book.Sheets(work_book.Sheets.Count)
Set work_book = Application.ActiveWorkbook
Set new_sheet = work_book.Sheets.Add(after:=last_sheet)
new_sheet.Name = dataSheet.Cells(i, "B").Value
ActiveSheet.Paste

ActiveSheet.Range("A1:H2").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=new_sheet.Range("A1:H2"), _
PlotBy:=xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:=new_sheet.Name
With ActiveChart

.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
Next i

Application.ScreenUpdating = True

End Sub
[/vba]

[maraippo]

The code give run time error 13, "type mismatch"
but able to produce the first chart though

the error highlighted is this-->
[VBA]
new_sheet.name = dataSheet.Cells(i, "B").Value[/VBA]

[maraippo]

I copy to another book and the code work again

Thanks for your help xld.

Maybe the old workbook has too many modules that i've tried on it...around 14 in it. Maybe it has conflicted or something.

I just have to start in the new workbook then

Thank you again xld