VBA/Macro

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

John McCoon

greetings.

i'm trying to write a vba to that is commandbutton click
event driven.

Simple task: copy two charts and pasting them in a newly
added sheet.

using Sheets.add procedure excel adds a sheet and
incrementally attaches a number to the name. first time
one clicks the command, say, sheet1 is inserted, next time
is sheet2 etc.

The problem I have is that I need to track which is the
latest sheet# that was added.

How do I do that?

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

I don't want to use activesheet.paste because I need to
refer to this newly added sheet later in this same
subroutine.

Thanks
John
 
find a cell for reference on one of your worksheets and include the following code
Range(your cell ex:"A1").selec
selection.value = sheet



----- John McCoon wrote: ----

greetings

i'm trying to write a vba to that is commandbutton click
event driven

Simple task: copy two charts and pasting them in a newly
added sheet

using Sheets.add procedure excel adds a sheet and
incrementally attaches a number to the name. first time
one clicks the command, say, sheet1 is inserted, next time
is sheet2 etc

The problem I have is that I need to track which is the
latest sheet# that was added

How do I do that

Private Sub commandbutton1_click(

ActiveSheet.ChartObjects("Chart 735").Activat
ActiveChart.ChartArea.Selec
ActiveChart.ChartArea.Cop
ActiveWindow.Visible = Fals

Sheets.Ad
'this will add a sheet

Sheet#.selec
activesheet.past

I don't want to use activesheet.paste because I need to
refer to this newly added sheet later in this same
subroutine

Thank
Joh
 
Hi,
I'm not clear what you tried to say.
can you explain it in a different way please.

Because added sheets are numbered automatically, I like to
be able to know what sheet number was added last.
Does this do that?
 
you can use the string in my earlier email to accomplish what you need

start with another sheet for storing your last sheet number used
you can name it anything you want. for my purposes, i'll call it "Data"

Private Sub commandbutton1_click(

ActiveSheet.ChartObjects("Chart 735").Activat
ActiveChart.ChartArea.Selec
ActiveChart.ChartArea.Cop
ActiveWindow.Visible = Fals

Sheets.Ad
'this will add a sheet
activesheet.name = zShee

zSheet.selec
activesheet.past

Sheets("Data").selec
Range("A1").selec
selection.value = zShee


----- JohnMcCoon wrote: ----

Hi
I'm not clear what you tried to say
can you explain it in a different way please

Because added sheets are numbered automatically, I like to
be able to know what sheet number was added last
Does this do that
 
Back
Top