maraippo
07-06-2008, 08:26 PM
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:
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
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 :)
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:
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
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 :)