Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

how to make the macro keep working after deleting the sheet2 and chart

  Asked By: Hayrah    Date: Sep 28    Category: MS Office    Views: 675
  

I made a macro to create pivot table and chart

problem: when I delete the sheet2 and chart1 and run that macro again
there are an error messages in line 9 which is Sheets("Sheet2").Select

question: how to make the macro keep working after deleting the sheet2
and chart1 because I want to make it again after modifying some data


Sub peopleutility()
'
' peopleutility Macro
' Macro recorded 2/24/2005 by wayan sumitra
'

'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R1085C20").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
Charts.Add
ActiveChart.Location Where:=xlLocationAsNewSheet
Sheets("Sheet2").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Employee(s)")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Actual work"), "Sum of Actual work", xlSum
Sheets("Chart1").Select
End Sub

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Amanda Carter     Answered On: Sep 28

When Excel creates worksheets and chart  it assigns a default name of
"worksheet" followed by a number. So the next sheet created will be
"worksheet" followed by number + 1.

Sheets("Chart1").Select

Refers to a specific sheet by name, alternatively you could use

Sheets(3).Select

To refer by position.

A better solution would be to rename the worksheet and chart after it is
created so you can refer to them by a specific name.

i = Sheets.Count
Sheets(i).Name = "PrimaryData"

Will rename the last worksheet in the workbook.

Play around with the macro  recorder to see how renaming works for the
worksheet and the chart.

 
Answer #2    Answered By: Sallie Hill     Answered On: Sep 28

Pls give Proper details regarding Chart.........

 




Tagged:            

 
 
 
 

Related Post