Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste from 1 sheet to another
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. -- poppy ------------------------------------------------------------------------ poppy's Profile: http://www.excelforum.com/member.php...o&userid=11453 View this thread: http://www.excelforum.com/showthread...hreadid=314351 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and Paste from one sheet to another | Excel Discussion (Misc queries) | |||
Copy from one Sheet and paste on another sheet based on condition | Excel Discussion (Misc queries) | |||
Copy Paste to another sheet | Excel Worksheet Functions | |||
Active Cell Copy And Paste Sheet to Sheet | New Users to Excel | |||
pls help!! copy -paste to another sheet. getting the sum | Excel Programming |