ET2005 's selective pasting has subtraction operations, except that there are no & operations (that is, connections), as shown in figure one. This makes certain requirements in the work difficult to meet.
If you need to enter the following content, because there is no connection function, you have to repeat the characters repeated input, undoubtedly reducing the efficiency.
Guangdong Dongguan full Hing Co., ltd
Dongguan City, Guangdong Province, Hong Yun Co., Ltd.
Guangdong Dongguan Zhongtian Co., Ltd.
Guangdong Dongguan Moon full Floor Co., ltd
The limited formula of Dongguan Xing and Sheng in Guangdong province
Use VBA for WPS Table 2005 to add a "selective paste-connection" function, placed in the right menu to facilitate the call.
Step 1: Create a macro for the feature "Paste selectively-connect"
This example assumes that your WPS has a VBA environment;
1. Create a new workbook;
2. Use the shortcut key alt+f11 to open the VBE environment;
3. Click the menu "Insert" module and enter the following code in the module:
Sub Selective Paste __ connection () Dimrngasstring,newshtasstring,oldshtasstring,texts,iasbyte,ansasbyte Ifactivesheet.protectcontentsthenmsgbox "Worksheet is protected, this program refuses to execute!", 64, "hint": Exitsub onerrorgotoendd rng=selection.address Oldsht=activesheet.name application.displayalerts=false worksheets.add nemsht=activesheet.name Range ("A1"). Select Activesheet.paste ifactivesheet.usedrange.count>1then MsgBox "can only connect the values of a single cell. ", Gotoendd," Andysky "EndIf texts=selection.text application.screenupdating=true Sheets (Oldsht). Select Ans=inputbox ("Please choose to connect to the target before or after the target.") "&CHR & 1: Before the target is connected;" _ &CHR & 2: After the target is connected. "," Connection mode ", 1 fori=1toselection.count if--ans=1thenselection (i) =textsselection (i) if--ans=2thenselection (i) = Selection (i) Texts Next endd:worksheets (Nemsht). Delete endsub
Step 2: Add macro functionality to the right-click menu
1. Use shortcut key Ctlr+r open "Project Explorer", find "ThisWorkbook" in the left pane and double-click;
2. In the Code window on the right, enter the code (two code: the first paragraph is to add the worker to the right-click menu, and the other section restores the right key when you close Excel):
Privatesubworkbook_open () withapplication.commandbars ("cell"). Controls.Add (type:=msocontrolbutton,before:=4,temporary:=true). caption= "Selective paste __ Connection (&paste)". onaction= "Selective paste __ Connection" Endwith endsub privatesubworkbook_beforeclose (Cancelasboolean) application.commandbars ("CELL" ). Reset endsub
Step 3. Save the workbook as a default template
1. Close the VBE window to return to the worksheet;
2. Click "File" "Save as" on the menu and select "wps form template file" at the Save type.
Step 4 Test Effect 1. Select Menu "File" "template on this machine", select the template just stored in the list;
2. Enter the following data in the worksheet (see figure V), and then copy the A9, then select A2:a7, right-click and select "Paste Selective link" in the menu;
3. At this point, the Program pop-up dialog box, see Figure Seven, enter 1 in the inside to connect to the front. Click OK and the final result is shown in Figure eight.
Conclusion: Et supports the two-time development of VBA, which makes it more flexible. When the default function is difficult to meet the needs of our work, we might as well use VBA to deal with it.