Help with sheets.add

  • Thread starter Thread starter John
  • Start date Start date
J

John

I got a response for this but I get error messages when I
run the subroutine. Hoping someone can provide another
solution.

This is what I need.

I have several charts that change based on various
parameters. I like to save these charts after I change
the scenarios via a commandbutton's click event.

then use Sheets.add procedure to add a new sheet everytime
the user clicks it, copy the charts and paste them onto
this newly added sheet.

The problem is that the charts are scattered in various
different sheets and so I need to know what is the name of
the most recently added sheet so that I can use worksheet
("XYZ").Select.... then .... paste..

How can I do that given this?

What I have so far is something like this:

Private Sub commandbutton1_click()

ActiveSheet.ChartObjects("Chart 735").Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
ActiveWindow.Visible = False

Sheets.Add
'this will add a sheet#

Sheet#.select
activesheet.paste

But I need to somehow refer to this sheet's name.

thanks
 
When a sheet is added, it becomes the activesheet, so you shouldn't need it,
but this is how you do it anyway

sheets.Add.name ="Bob"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi John,
Don't know if this applies or not, but if you have a worksheet and
a chart related to each other, you can select both (grouping), then
Copy the sheets. Don't forget to ungroup immediately by right click
on a worksheet tab and ungroup. The newly copied chart and sheet
will be related to each other and you can make your changes there.
 
Thank you Bob,
This would work if I could capture the name that is
automatically assigned (like sheet1, etc..) or if I can
assign names incrementally and then keep track of what is
the last sheet's name somehow.
How can I do that?
 
thank you David,
Bob suggests me using sheets.add.name = "bob" for instance.
Rather than assign a name to this newly created sheet. How
can I keep track of the name so that I can refer to it by
code?
-----Original Message-----
Hi John,
Don't know if this applies or not, but if you have a worksheet and
a chart related to each other, you can select both (grouping), then
Copy the sheets. Don't forget to ungroup immediately by right click
on a worksheet tab and ungroup. The newly copied chart and sheet
will be related to each other and you can make your changes there.


"John" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Do you really need to know the name, or could you just use:

Dim wsMySheet As Worksheet
Set wsMySheet = Worksheets.Add

and use the variable wsMySheet to refer to that sheet?
 
I think my suggestion of copying sheet and graph together
manually is not what you are looking for.
Suggestion was made not knowing if the programming was
important or was just used to create a copy.
 
David
I was not very clear. My appologies. And thank you much
for helping.
Yes, I don't want to do this manually, like to code it.
John
 
Back
Top