Add Paste for WPS table--Connect

Source: Internet
Author: User
Tags chr

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.

Customer Address

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

Solution Idea:

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 application.displayalerts=false worksheets.add 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.

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.