How to use Excel VBA to achieve "copy and paste" (Part 1), Uncle Xin Excel VBA Phase 1

The content of the first issue is according to the requirements of@小本, explaining how to use VBA to automatically copy and paste from page A to page B. Originally, I didn't plan to divide it into two episodes, but in the first issue, I always have to explain what VBA is and what to pay attention to when using it. Although the programming environment is not complicated, I still need to mention it. So it can only be divided into upper and lower~

🔷Introduction 🔷

❇️ What is VBA: A programming language mainly used in Microsoft Office, formerly known as Visual Basic for Application. Here we mainly discuss how to execute and edit VBA in Excel.

❇️ What Excel VBA can do: realize automation/semi-automation, and turn what you need to do step by step with the mouse and keyboard to be done by clicking a button. Can send emails, organize data, generate web pages, most of which are repetitive operations.

❇️ Applicable people: People who often use Excel at work, lack a programming background but want to speed up their work efficiency.

🔷 Programming interface and trying to run a macro 🔷

❇️ The first step, open the editor: After opening Excel, press the shortcut key (Alt + F11) to open the VB editor (where to edit and execute VBA code).

❇️ Step 2, Insert Module: Right-click on "Microsoft Excel Objects" , and select "Insert" >> "Module" in the form.

❇️ The third step, paste the code: Double-click "Module1" with the left mouse button, and paste the following code into the editing window of "Module1" .

 Sub Macro1()
    Sheets("B").Cells(1, 2) = 1
    Sheets("C").Cells(1, 3) = Sheets("B").Cells(1, 2)   
    MsgBox "Hello World!"  
End Sub

❇️ Step 4, execute the program: left-click the green right arrow on the toolbar to run the code, the number "1" appears in cell A1 of page B, and the number "1" appears in cell C1 of page C, and A pop-up window displays the programming classic text "Hello world!"

❇️ What should I do if I click the arrow instead of running the code, but pop out of the window? Select the program to be executed (there is only one option here), and then click "Run" to execute.

🔷Sample download in the text🔷

• If the above is not successful, you can also download the template to try it out, click me to download .
• After opening the file, pay attention to whether there is a warning in the picture below. This is to ask the user to confirm whether the macro is credible, so as to avoid executing the problematic macro. Click "Enable Content" to use it normally.
• "Macro1" in the picture below is the example this time, and "Macro2" is the example next time. Those who are interested can have a sneak peek.

🔷Example VBA code explained 🔷

 Sub Macro1()
End Sub

• A complete macro (Macro in English, it seems to be called "Macro" in China), the format is Sub at the beginning, End Sub at the end, "Macro1" can be changed to the name you like (it cannot start with a number, and there should be no spaces between the names, more There can be no special symbols other than "_", preferably in English, especially for people like me who use English systems for computers), please refer to this article for precautions. The content to be run is placed between them.
• The definition of a macro is a piece of executable VBA code. At this stage, you only need to know that every program must have it.

 Sheets("B").Cells(1, 2) = 1

• Sheets("B"): Represents page B.
• Cells(1,2): Represents cell B1 (first row, second column).
• The entire line of code will enter the number 1 into cell B2 of page B (first row, second column)

 Sheets("C").Cells(1, 3) = Sheets("B").Cells(1, 2)   

• This line of code is to make the cell C1 (the first row, the third column) of the page C equal to the cell B2 (the first row, the second column) of the page B, and achieve the effect of copy and paste in this way.

 MsgBox "Hello World!"  

• A window pops up that says "Hello World!" .
• The first class of each programming class must have content, and the reasons refer to Baidu Encyclopedia .

🔷 Set macros to run for keys 🔷

In general, VBA teaching books like to use formal but ugly buttons, but I don't teach them.

❇️ The first step is to find a pattern you like: if you don't like it, you can just put a picture you like instead.

❇️ Step 2, right-click the button and select "Assign Macro..."

❇️ The third step, click the macro that you want the button to execute, and then click "Ok", so that the macro will be executed when the button is clicked later.

🔷 Next issue notice 🔷

Next time, on the basis of this issue, apply the loop structure to repeat the "copy and paste".

🔷 Tucao nonsense 🔷

• It is very difficult to write Chinese teaching in the English version of Excel. I don’t know what Chinese are called for many functions. I have to refer to Chinese teaching books.
• This issue actually skipped a lot of settings that should have been made, but I am worried that you will directly turn off the settings when you see so many, so I will add them later (if there are any).