I put this in a General module:

Option Explicit
Public aaaa As String
Sub aa()
aaaa = "this is a test"
End Sub

And I ran it to initialize that public aaaa variable before I clicked the button
that ran your code.

I added a couple of lines to your code (debug.print's):

Option Explicit

Private Sub b_Copy_Click()
Dim i_Sheet As Integer
Dim i_ExistSheet As Integer
Dim c_Password As String

Debug.Print "Befo " & aaaa

c_Password = "xx"

i_Sheet = 1
i_ExistSheet = 0

If ActiveSheet.Name = "Data Entry" Then
Do While i_Sheet <= Sheets.Count
If Sheets(i_Sheet).Name = "Temp" Then
i_ExistSheet = i_Sheet
End If
i_Sheet = i_Sheet + 1
Loop
If i_ExistSheet 0 Then
If MsgBox("The existing Temp sheet will be deleted, before the copy.", _
vbOKCancel) = vbOK Then
Application.DisplayAlerts = False
Sheets(i_ExistSheet).Delete
i_ExistSheet = 0
Application.DisplayAlerts = True
End If
End If
If i_ExistSheet = 0 Then
ActiveSheet.Copy After:=Sheets("Legend")
ActiveSheet.Name = "Temp"
If ActiveSheet.Name = "Temp" Then
ActiveSheet.Unprotect Password:=c_Password
ActiveSheet.OLEObjects("b_Copy").Delete
ActiveSheet.OLEObjects("Copy_Prev_Row").Delete
MsgBox ("Delete this temporary sheet after finishing working with it.")
End If
End If
End If
Debug.Print "after: " & aaaa
End Sub

I got this back:
Befo this is a test
after: this is a test

But if you're having trouble keeping the variables, then you could define a
boolean in that same general module.

Public VarsAreInitialized as boolean

Then check that each time you need to rely on them

if varsareinitialized = false then
call routinethatinitializesvariables
end if

Sub routinethatinitializesvariables()
varsareinitialized = true
'rest of vars here
end sub

If you're depending on variables that may change after initialization, then
maybe putting them on a worksheet would be the way to go.

johnny wrote:

On Feb 27, 7:21 am, Dave Peterson wrote:
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

Sorry, I did not post all the code:

Here is the complete one:

Private Sub b_Copy_Click()
Dim i_Sheet As Integer
Dim i_ExistSheet As Integer

i_Sheet = 1
i_ExistSheet = 0

If ActiveSheet.Name = "Data Entry" Then
Do While i_Sheet <= Sheets.Count
If Sheets(i_Sheet).Name = "Temp" Then
i_ExistSheet = i_Sheet
End If
i_Sheet = i_Sheet + 1
Loop
If i_ExistSheet 0 Then
If MsgBox("The existing Temp sheet will be deleted, before the
copy.", vbOKCancel) = vbOK Then
Application.DisplayAlerts = False
Sheets(i_ExistSheet).Delete
i_ExistSheet = 0
Application.DisplayAlerts = True
End If
End If
If i_ExistSheet = 0 Then
ActiveSheet.Copy After:=Sheets("Legend")
ActiveSheet.Name = "Temp"
If ActiveSheet.Name = "Temp" Then
ActiveSheet.Unprotect Password:=c_Password
ActiveSheet.OLEObjects("b_Copy").Delete
ActiveSheet.OLEObjects("Copy_Prev_Row").Delete
MsgBox ("Delete this temporary sheet after finishing working
with it.")
End If
End If
End If

End Sub

--

Dave Peterson