copy chart area

Hi All,

I need to make a copy of a template sheet, containing a chart area, which in
turn contains a plot area.
I create a new sheet, rename it, then copy contents of a template as
follows -
Sheets(TemplateSheet).Activate
ActiveSheet.UsedRange.Select
Selection.Copy
With ActiveWorkbook.Sheets
.Add After:=Worksheets(Worksheets.count)
End With
NewSheetName = "newname"
ActiveSheet.Name = NewSheetName
ActiveSheet.Paste
It copies all the spreadsheet, but no plot shows up on a copy.

How can I do that?

Thanks!

Vik -

You're making a new sheet, and copying part of another sheet onto the
blank new sheet. Why not use a copy of the whole sheet instead:

Sheets(TemplateSheet).Copy After:=Worksheets(Worksheets.count)
NewSheetName = "newname"
ActiveSheet.Name = NewSheetName

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Vik wrote:

> Hi All,
>
> I need to make a copy of a template sheet, containing a chart area, which in
> turn contains a plot area.
> I create a new sheet, rename it, then copy contents of a template as
> follows -
> Sheets(TemplateSheet).Activate
> ActiveSheet.UsedRange.Select
> Selection.Copy
> With ActiveWorkbook.Sheets
> .Add After:=Worksheets(Worksheets.count)
> End With
> NewSheetName = "newname"
> ActiveSheet.Name = NewSheetName
> ActiveSheet.Paste
> It copies all the spreadsheet, but no plot shows up on a copy.
>
> How can I do that?
>
> Thanks!
>
>

Jon -

Thank you, works great.
Can I ask for one more tip?
How can I give a new name to the chart on a copied sheet?

Thanks,
- Vik

"Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message
news:%23astq4glFHA.3656@TK2MSFTNGP09.phx.gbl...
> Vik -
>
> You're making a new sheet, and copying part of another sheet onto the
> blank new sheet. Why not use a copy of the whole sheet instead:
>
> Sheets(TemplateSheet).Copy After:=Worksheets(Worksheets.count)
> NewSheetName = "newname"
> ActiveSheet.Name = NewSheetName
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> Vik wrote:
>
> > Hi All,
> >
> > I need to make a copy of a template sheet, containing a chart area,
which in
> > turn contains a plot area.
> > I create a new sheet, rename it, then copy contents of a template as
> > follows -
> > Sheets(TemplateSheet).Activate
> > ActiveSheet.UsedRange.Select
> > Selection.Copy
> > With ActiveWorkbook.Sheets
> > .Add After:=Worksheets(Worksheets.count)
> > End With
> > NewSheetName = "newname"
> > ActiveSheet.Name = NewSheetName
> > ActiveSheet.Paste
> > It copies all the spreadsheet, but no plot shows up on a copy.
> >
> > How can I do that?
> >
> > Thanks!
> >
> >

Naming a Chart:

http://peltiertech.com/Excel/ChartsH...ameAChart.html

ActiveSheet.ChartObjects(1).Name = "New Chart Name"

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Vik wrote:

> Jon -
>
> Thank you, works great.
> Can I ask for one more tip?
> How can I give a new name to the chart on a copied sheet?
>
> Thanks,
> - Vik
>
> "Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message
> news:%23astq4glFHA.3656@TK2MSFTNGP09.phx.gbl...
>
>>Vik -
>>
>>You're making a new sheet, and copying part of another sheet onto the
>>blank new sheet. Why not use a copy of the whole sheet instead:
>>
>> Sheets(TemplateSheet).Copy After:=Worksheets(Worksheets.count)
>> NewSheetName = "newname"
>> ActiveSheet.Name = NewSheetName
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>
>>Vik wrote:
>>
>>
>>>Hi All,
>>>
>>>I need to make a copy of a template sheet, containing a chart area,
>
> which in
>
>>>turn contains a plot area.
>>>I create a new sheet, rename it, then copy contents of a template as
>>>follows -
>>> Sheets(TemplateSheet).Activate
>>> ActiveSheet.UsedRange.Select
>>> Selection.Copy
>>> With ActiveWorkbook.Sheets
>>> .Add After:=Worksheets(Worksheets.count)
>>> End With
>>> NewSheetName = "newname"
>>> ActiveSheet.Name = NewSheetName
>>> ActiveSheet.Paste
>>>It copies all the spreadsheet, but no plot shows up on a copy.
>>>
>>>How can I do that?
>>>
>>>Thanks!
>>>
>>>
>
>
>

Jon,

Perfect site.
I meant changing a caption, which means -

ActiveSheet.ChartObjects(1).Chart.ChartTitle.Caption = "newname"

But I figured that out with your help.

Thanks!
- Vik

"Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message
news:uU1q95vlFHA.4000@TK2MSFTNGP12.phx.gbl...
> Naming a Chart:
>
> http://peltiertech.com/Excel/ChartsH...ameAChart.html
>
> ActiveSheet.ChartObjects(1).Name = "New Chart Name"
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> Vik wrote:
>
> > Jon -
> >
> > Thank you, works great.
> > Can I ask for one more tip?
> > How can I give a new name to the chart on a copied sheet?
> >
> > Thanks,
> > - Vik
> >
> > "Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message
> > news:%23astq4glFHA.3656@TK2MSFTNGP09.phx.gbl...
> >
> >>Vik -
> >>
> >>You're making a new sheet, and copying part of another sheet onto the
> >>blank new sheet. Why not use a copy of the whole sheet instead:
> >>
> >> Sheets(TemplateSheet).Copy After:=Worksheets(Worksheets.count)
> >> NewSheetName = "newname"
> >> ActiveSheet.Name = NewSheetName
> >>
> >>- Jon
> >>-------
> >>Jon Peltier, Microsoft Excel MVP
> >>Peltier Technical Services
> >>Tutorials and Custom Solutions
> >>http://PeltierTech.com/
> >>_______
> >>
> >>
> >>Vik wrote:
> >>
> >>
> >>>Hi All,
> >>>
> >>>I need to make a copy of a template sheet, containing a chart area,
> >
> > which in
> >
> >>>turn contains a plot area.
> >>>I create a new sheet, rename it, then copy contents of a template as
> >>>follows -
> >>> Sheets(TemplateSheet).Activate
> >>> ActiveSheet.UsedRange.Select
> >>> Selection.Copy
> >>> With ActiveWorkbook.Sheets
> >>> .Add After:=Worksheets(Worksheets.count)
> >>> End With
> >>> NewSheetName = "newname"
> >>> ActiveSheet.Name = NewSheetName
> >>> ActiveSheet.Paste
> >>>It copies all the spreadsheet, but no plot shows up on a copy.
> >>>
> >>>How can I do that?
> >>>
> >>>Thanks!
> >>>
> >>>
> >
> >
> >