How to Copy Entire Row & Column Data into Another Sheet by Macro without Coding

In our work we often need to insert or update entire row or column data into several different sheets. For example, for recording score or consumption, we usually have a main sheet to record the Total or Summary, and following sheets save the details like each student’s score or one production’s consumption. So when we update the total, how can we update the other sheets conveniently without inserting data or update data one by one?

This article will introduce you one method to copy entire row to another sheets by Macro. If you have coding experience you can choose VBA Macro, or you can record Macro to implement the function without any coding.

Initial Condition

First, we need to prepare a MAIN SHEET to save all students’ name, date and score. Then prepare a COPY SHEET to save each student’s score.

Move or Update Entire Row & Column Data 1

MAIN SHEET and COPY SHEET ‘Michelle’.

Move or Update Entire Row & Column Data 2

Copy Entire Row into Another Sheet by Macro


Now follow below steps, let’s get started to learn how can we insert entire row from main sheet into copy sheet OR update both sheets by Macro.
Step 1. First we need to enable Developer.
a. Click on Excel->Preferences to enter Excel Preferences window.

Move or Update Entire Row & Column Data 3

b. Click on View under Authoring.

Move or Update Entire Row & Column Data 4

c. Check on Developer tab option under In Ribbon, Show.

Move or Update Entire Row & Column Data 5

d. Then you can find Developer is displayed in your tool bar. The steps are based on Microsoft Excel 2016 for MAC OS. If you have different versions for Excel on other OS like Windows, you may find Developer in Excel Options.

Move or Update Entire Row & Column Data 6

Step 2. Now we already enabled Developer tab. So click on Developer->Button, this step is used for creating a button, and we will assign a Macro for this button latter.

Move or Update Entire Row & Column Data 7

Step 3.  After clicking on Button, use your mouse to select any area on current sheet to create a button. The button size depends on the area your mouse selected. After releasing your mouse, Assign Macro window pops up.

Move or Update Entire Row & Column Data 8

In this step, Excel will assign the button a Macro after creating the button by default. If you already have existing Macros, you can enter its name into Macro Name or select one from Macro list, click OK to assign this Macro. Then when you clicking on the button, it will call the Macro. In above screenshot, Macro1, Macro6 and Macro7 are existing Macros, if this is your first time to use Macro, there is nothing listed, see below. Just click on OK, then a button is created without any related Macro.

Move or Update Entire Row & Column Data 9

Step 4. Edit the button name by right click on it, select ‘Edit Text’. Or you can double click on the button to update button name directly. In this article, we name it Update.

Move or Update Entire Row & Column Data 10

Now we have an Update button.

Step 5. Preconditions are done now. Let’s record a Macro. Click on Developer->Record Macro.

Move or Update Entire Row & Column Data 11

Step 6. In pop up Record Macro window, enter Macro name, we name it Button_Update. Then click OK.

Move or Update Entire Row & Column Data 12

Step 7. In the left corner of bottom, we can see there is Ready with Macro icon.

Move or Update Entire Row & Column Data 13

Step 8. Click on Macro icon, then we start to record our Macro.
a. Select on Column A,B,C.

b. Click on Data->Filter, Filter arrow is displayed on cell Name\Date\Score.

c. Click on Filter arrow on Name, then only check on Michelle (which is need to be saved into copy sheet.), close filter name window. Now only rows showing Michelle’s scores are loaded.

d. Copy these rows into copy sheet Michelle.

e. Go back to MAIN SHEET. Click on Data->Filter to clear filter. Now hidden rows for David are displayed again.

Step 9. In step#8, we do a complete record for Macro Button_Macro. Click Stop icon in the bottom to stop record.

Move or Update Entire Row & Column Data 14

Step 10. Assign Macro we just created for button Update. Right click on button Update, click on Assign Macro. Enter Macro Name Button_Update. Then click OK.

Move or Update Entire Row & Column Data 15

Step 11. Now we can test if button Update can implement our expectation.
a. Insert a new row of Data for Michelle in MAIN SHEET.

Move or Update Entire Row & Column Data 16

b. Click on button Update.

c. After executing Macro, check copy sheet Michelle.

Move or Update Entire Row & Column Data 17

The new entire row is inserted into Michelle successfully. That means Update button is usable.

Notes:
1. Sometimes there is no response for Excel when executing Record Macro or Run Macro, just wait for a while and it will recover and continue the following steps.
2. After running Macro, you need to refresh your MAIN SHEET because sometimes it displays improperly like new row with its following rows are invisible.

Move or Update Entire Row & Column Data 18

Related Posts

If Cell is This Value or That Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of logical test. If you want to see if a cell is A or B, and if one of ...

If Value is Greater Than A Certain Value
If Value is Greater Than A Certain Value 1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the logical test result. If you want to see if a value in one cell is greater than a specific value, ...

If Cell is Not Blank
If Cell is Not Blank 6

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

VBA Macro For VLOOKUP From Another Sheet
vba macro for vlookup from another sheet1

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data ...

If Cell is Blank
If Cell is Blank_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell Equals Certain Text String
If cell equals certain text_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if cell equals a certain text string like “Win”, you ...

If Cell Contains Either Text1 or Text2
If cell contains text1 or text2_1

IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to see if cell contains certain substring1 like “abc” or substring2 like “def”, and returns true ...

If Cell Contains Certain Text OR Equals Certain Text

IF cell equals certain text IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to test values to see if they equal certain text like ...

VLOOKUP From Another Sheet Not Working
vlookup from another sheet not working3

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working ...

If Cell Begins with One of Three Supplied Characters
If Cell Begins with One of Three Supplied Characters

If you want to test values to see if they begin with some given specific characters like “x”, ”y”, or “z”, you can create a formula with COUNTIF and SUM functions to return results. EXAMPLE You can see “TRUE” or ...

Sidebar