Hi,

When I use ActiveSheet.Copy to copy the current sheet to another
sheet, all the public variables or global variables are reset to 0,

anyway to avoid this or keep the public variables values when coping ?

Thanks.

Johnny.

I've never seen this happen.

Can you post the snippet of code that is affected?

Any chance that you have a misbehaving event macro that's really doing the dirty
work of clearing those variables.

johnny wrote:

Hi,

When I use ActiveSheet.Copy to copy the current sheet to another
sheet, all the public variables or global variables are reset to 0,

anyway to avoid this or keep the public variables values when coping ?

Thanks.

Johnny.

--

Dave Peterson

On Feb 26, 8:20 pm, Dave Peterson wrote:
I've never seen this happen.

Can you post the snippet of code that is affected?

Any chance that you have a misbehaving event macro that's really doing the dirty
work of clearing those variables.

johnny wrote:

Hi,

When I use ActiveSheet.Copy to copy the current sheet to another
sheet, all the public variables or global variables are reset to 0,

anyway to avoid this or keep the public variables values when coping ?

Thanks.

Johnny.

--

Dave Peterson

I just found out from other site, said if the vb code got edited, all
the global variables will be reset.
As there are procedures on the current sheet, if I copy to other
sheet, all the code behind it will be copied too.
I think this cause all the global variables reset to default. I need
to copy the variable to a temp cells and retrieve back the value after
the sheet copy.

Is anyway just copy only the content of the current sheet to the other
sheet ?


Thanks.

Johnny.

What you've described isn't editing the code.

And that isn't really true. If you execute an "End" line (not "End if", not
"End Sub", not "End Function", but a simple "End"), then the public variables
will be reset. And if you hit the Reset button (or Run|Reset in the VBE), the
same thing will happen.

So I'm not sure what you saw is applicable to your situation.

Is there a chance that you're looking at variables in the new workbook--not the
variables in project that contained the macro that was running?



johnny wrote:

On Feb 26, 8:20 pm, Dave Peterson wrote:
I've never seen this happen.

Can you post the snippet of code that is affected?

Any chance that you have a misbehaving event macro that's really doing the dirty
work of clearing those variables.

johnny wrote:

Hi,

When I use ActiveSheet.Copy to copy the current sheet to another
sheet, all the public variables or global variables are reset to 0,

anyway to avoid this or keep the public variables values when coping ?

Thanks.

Johnny.

--

Dave Peterson

I just found out from other site, said if the vb code got edited, all
the global variables will be reset.
As there are procedures on the current sheet, if I copy to other
sheet, all the code behind it will be copied too.
I think this cause all the global variables reset to default. I need
to copy the variable to a temp cells and retrieve back the value after
the sheet copy.

Is anyway just copy only the content of the current sheet to the other
sheet ?

Thanks.

Johnny.

--

Dave Peterson

On Feb 26, 8:47 pm, Dave Peterson wrote:
What you've described isn't editing the code.

And that isn't really true. If you execute an "End" line (not "End if", not
"End Sub", not "End Function", but a simple "End"), then the public variables
will be reset. And if you hit the Reset button (or Run|Reset in the VBE), the
same thing will happen.

So I'm not sure what you saw is applicable to your situation.

Is there a chance that you're looking at variables in the new workbook--not the
variables in project that contained the macro that was running?



johnny wrote:

On Feb 26, 8:20 pm, Dave Peterson wrote:
I've never seen this happen.

Can you post the snippet of code that is affected?

Any chance that you have a misbehaving event macro that's really doing the dirty
work of clearing those variables.

johnny wrote:

Hi,

When I use ActiveSheet.Copy to copy the current sheet to another
sheet, all the public variables or global variables are reset to 0,

anyway to avoid this or keep the public variables values when coping ?

Thanks.

Johnny.

--

Dave Peterson

I just found out from other site, said if the vb code got edited, all
the global variables will be reset.
As there are procedures on the current sheet, if I copy to other
sheet, all the code behind it will be copied too.
I think this cause all the global variables reset to default. I need
to copy the variable to a temp cells and retrieve back the value after
the sheet copy.

Is anyway just copy only the content of the current sheet to the other
sheet ?

Thanks.

Johnny.

--

Dave Peterson

I inserted a new module and put all public variables there.
I put a copy button on one sheet.
on the click event :

ActiveSheet.Copy After:=Sheets("Temp1")
ActiveSheet.Name = "Temp"
If ActiveSheet.Name = "Temp" Then
ActiveSheet.Unprotect Password:=c_Password
ActiveSheet.OLEObjects("b_Copy").Delete


after click on the copy button, and new sheet created, also all the
variables are reset.

All happens in one workbook.

thanks.

Johnny.

And if you do this a second time, the line that tries to rename the newly copied
sheet as Temp will fail. If you hit the End option on the Debug dialog, you'll
lose the contents of the variables.

And it looks like you're not showing all the code. It looks to me that you have
an "On error resume next" line that avoids the rename error. Maybe something in
the code you're not sharing is the problem.

johnny wrote:

snipped
I inserted a new module and put all public variables there.
I put a copy button on one sheet.
on the click event :

ActiveSheet.Copy After:=Sheets("Temp1")
ActiveSheet.Name = "Temp"
If ActiveSheet.Name = "Temp" Then
ActiveSheet.Unprotect Password:=c_Password
ActiveSheet.OLEObjects("b_Copy").Delete

after click on the copy button, and new sheet created, also all the
variables are reset.

All happens in one workbook.

thanks.

Johnny.

--

Dave Peterson