Copy and Paste from 1 sheet to another

[poppy]

Hi Experts

I am working on a report that draws data from an essbase database int
excel. I am doing my programming in VBA using the Essbase Add-In an
functions. My sheet has a textbox that a user has to input information
Everytime the data in the textbox changes, a template in the secon
sheet must be copied and pasted into the master sheet. Then the data i
the textbox must be transfer to cell B4, then using that data it shoul
retrieve the necessary info from the db.

I tried using the change event of the textbox but it does not allow m
to enter the full word I'm looking for e.g. If I'm looking for fish02
as soon as I type in "f" then it dumps f into cell"B4" and tells me i
cannot find what i'm looking for, yet i know it's there.

Now I am using the lostfocus event of the textbox so it waits until
click outside the box to retrieve my info.

My problem is that the copy procedure for the template does not work
This is the code giving me the problem:

Code
-------------------
Sub Member_Select()
Worksheets("Template").Select
Columns("A:E").Select
selection.Copy
Worksheets("Mainsheet").Select
ActiveSheet.Paste 'This is the problem line

Range("B4").Select
x = EssMenuVMemberSelection()
x = EssVRetrieve(Null, Null, Null)
End Su
-------------------


I do know what the problem is, but don't have a solution. Everytime th
code hits the Mainsheet select line, it goes to the procedure below an
the copy is lost.

Code
-------------------
Private Sub Worksheet_Activate()
Dim vt As Variant ''This is going to hold the results - most likely will be an array of member names
Dim x As Long ''All Essbase functions return a long integer
Dim hCtx As Variant ''This will hold the connection information about the sheet
Dim response '''This will hold the answer to the question "connect now?"
Dim y As Long '''Another long to use for Essbase

Range("B3").Select
selection.NumberFormat = "@"

hCtx = EssVGetHctxFromSheet(Null) ''Check for connection to essbase"
If hCtx = 0 Then ''No connections - prompt to connect now
response = MsgBox("You are not connected to Essbase, would you like to connect now?", vbYesNo + vbInformation, "No Connection")
If response = vbYes Then ''You said "yes I would like to connect now
y = EssVConnect(Null, Null, Null, Null, Null, Null) ''using all nulls forces a login box
If y <> 0 Then '''You couldn't connect - exit out of subroutine
MsgBox "There was an error connecting to Essbase. Please try again.", vbOKOnly + vbCritical
Exit Sub
End If
Else
'''you said you didn't feel like connecting now - exit out of sub
MsgBox "Please connect to the database before using this sheet", vbOKOnly + vbInformation
Exit Sub
End If

End If

''This function returns information about a member - in this case a 2 indicated all of the descendants of "Date"
vt = EssVGetMemberInfo(Null, "Date", 2, True) ''true indicates by alias
cboDate.List = vt ''assign the dropdown box the values from the date info
cboDate.AddItem "Date", 0
x = EssVFreeMemberInfo(vt) ''release the array of member names now that they are written into the combo box

''Do the same thing for the Month drop downs
vt = EssVGetMemberInfo(Null, "Month", 2, True)
cboMonth.List = vt
cboMonth.AddItem "Month", 0
x = EssVFreeMemberInfo(vt)

' '''Now that the drop downs are full retrieve initial values onto the sheet
' Range("A1").Select
' x = EssVRetrieve(Null, Null, Null)
'
'x = EssVSetSheetOption(Null, 1, 1)
'x = EssVSetSheetOption(Null, 2, False)
'x = EssVSetSheetOption(Null, 6, True)
'x = EssVSetSheetOption(Null, 11, False)
'x = EssVSetSheetOption(Null, 7, True)
''x = EssVSetSheetOption(Null, 18, True)
'x = EssVSetSheetOption(Null, 24, True)
'If x = 0 Then
' ' MsgBox ("#Missing values will appear. ")
'Else
' MsgBox ("Error. #Missing option not set.")
'End If
End Su
-------------------


The thought of jumping thru my office window is sounding more and mor
appealing so If anyone has any ideas please help.

Kind Regards.