Quick method for extracting office data from excel

Method 1: extract one by one using shortcuts

The effect of this instance is: after clicking the cell to extract data, you just need to press Ctrl + d to automatically copy the data to the specified position and continue to operate, the copied data is automatically extended to the next cell at the specified position.

Step 1

Start excelto create a new file and save it as datacenter.xls. Click the "tool & rarr; macro & rarr; record new macro" menu command to set the macro name to CopyRange and the shortcut key to Ctrl + d (note: do not conflict with the system shortcut key ), click "OK.

Step 2

Click the "tool & rarr; macro & rarr; stop recording" menu command. How can I stop recording at the beginning? In this way, you only need to obtain a macro framework with a shortcut key and enter the specific code. Press Alt + F11 to open the Visual Basic editor window, double-click Module 1 in the project pane, delete the final "End Sub" statement, and add the following code:

The code is as follows:

Copy code

Selection. Copy 'copy the selected cell TheSel = ActiveCell. Address 'stores the cell location For I = 1 To 100 ', you can set the parameters H, 1, and 1st, starting from row 100 of column H and stopping row 100. If Range ("H" & I) = "" then' If column h I acts empty Range ("H" & I). Select the cell in the row I of column H. ActiveSheet. Paste 'paste cells Range (TheSel). Select 'back to the copy location GoTo line1 'jump out of loop End If Next I Line1: End Sub Step 3

Close the Visual Basic editor window and return to Excel. Copy the other Excel table to datacenter.xls, click the cell to extract data, and press Ctrl + d to copy it to row 3 of column H, and extract other data.

Method 2: multiple cells are extracted simultaneously.

The above method can only extract one cell at a time, and then add a simple macro to extract multiple cells at a time.

Step 1

First, assume that the table format of the data to be extracted is shown in the figure. Record a new macro named CopyAll. Set the shortcut key to Ctrl + e and enter the following code in the macro (do not delete "End Sub" this time "):

 

Figure 1 table example

The code is as follows:	Copy code
Range ("B1"). Select the data after "name" Copyrange' macro in the previous method called, the same below Range ("B2"). Select the data after "gender" CopyRange Range ("B3"). Select the data after "age" CopyRange

The parameter in the Range function indicates the location of the cell you want to extract data from. You can set it as needed.

Step 3

Return to Excel. In cells B1, B2, and B3, enter "Zhang Xiaole", "male", and "18 ". Press Ctrl + e, and the data will soon be copied to H1 ~ In the H3 cell. You can continue to extract other data.