Menu

How To Copy Data from One Excel Sheet to Another Using a Formula

Ask a question
Many Excel users struggle when trying to figure out how to move data from an entry sheet onto an archived sheet. Although Excel is a spreadsheet designed to perform complex calculations, such as mortgage amortization tables or tracking sales figures, most people never explore its more in-depth operations.

This FAQ will walk you through how to qualify and move your data.

Copying Data from One Excel Sheet to Another with a Formula

Begin by opening up the developer tab in Excel.


Next, you need to construct the Sheet Controller UserForm. This UserForm allows you to select where the data is moved from, where the data is moved to, what column to inspect for the qualifying data to determine if it moves, and what the value of the column entry should be to move it.

When creating your UserForm, mirror the following example, taking care to name each control correctly:









Now that you have constructed the Userform, you need to attach code to each control. In the UserForm, double-click the control named CommandButton2. The diagram of the Userform should no longer be visible, and you should now be presented with the code explorer, with the default code block. This particular code block starts with Private Sub ComandButton2_Click(). Place your cursor under the first line, but before the line that says End Sub. Now, enter the following code to set up the public variables to be applied to the rest of the code:


Now, go back to the UserForm Explorer, and double-click the control marked CommandButton3. Once again, the UserForm gets put aside for the code explorer. Place your cursor on the newly created code block, and enter the following code:


In the Explorer section of the Project Explorer, right-click Microsoft Excel Objects. Select Insert > Module.


Double-click the module called Module1, and type in the following public variables:


Now, go ahead and insert three more sheets into your workbook. You should now have four sheets, named Sheet1, Sheet2, Sheet3, and Sheet4.

On Sheet1, place items into about 10-15 lines, using the below image as an example of the test data:


Next, in the Developer tab (on top of the workbook), click Controls > Insert > the button icon:


Now, place the button anywhere on your sheet. When it asks about macros, select New.

You will notice that it placed the new macro in Module2 of the project. Highlight the code block in Module2, cut it from Module2. Now, double-click Module1. When the Code Explorer opens, right-click and select Paste. You should, now, have an empty code block that reads:


Place your cursor inside of the code block, and add the following code:


You will notice that there is a function called Buildform. This sets up the UserForm for the appropriate number of sheets, after taking inventory of them. To apply this, place the following code into the Code Explorer, below the Button1_Click() subroutine:


Within the buildform function, there is another function called Counttabs. You should place this code above the Buildform code, but below the Button1_click subroutine:


If both the TabFrom and TabTo variables are set, you will, then, need to run the createNew() function. Place the below code into the Code Explorer, above the Button1_click subroutines:



If you have chosen to create a new sheet, change the TabTo variable to the new sheet name. You will then need to run the LoopForMove(TabFrom, TabTo) routine. In the Code Explorer, enter the following code:


To find the last row of your sheet, enter the below code into the Code Explorer, above LoopForMove(FromWhatSheet, ToWhatSheet):


Now, you will be able to move the actual code using the Moveit(FromWhatSheet, CellLoc, ToWhatSheet, CutVal) function. Between the LooForMove() and createNew() functions, place the following code:

|fancy]
Continue to loop through each line on the From sheet, looking for qualified entries, using a For Loop function.

To sum up the actions of a UserForm, see the following illustration:


Here is the same illustration with, this time, with more elements taken into consideration:


Finally, here is the aforementioned code in its entirety: 


Option Explicit
Public TabFrom
Public TabTo
Public Qualif As String
Public WhatCol
Public WhatLogic
Public CutVal

Public FormXcel


Function FindLastRow(OnWhatsheet)
    FindLastRow = Cells(ThisWorkbook.Worksheets(OnWhatsheet).Rows.Count, 1).End(xlUp).Row
End Function

Function LoopForMove(FromWhatSheet, ToWhatSheet)
        Dim LastRow, Cnt
        Dim CellValue As String
        Dim CellLoc
        Dim nret
            If WhatCol = "" Then
                WhatCol = "A"
            End If
            If Qualif = "" Then
                   Qualif = "X"
            End If
            ThisWorkbook.Worksheets(FromWhatSheet).Select
            LastRow = FindLastRow(FromWhatSheet)
            For Cnt = LastRow To 1 Step -1
                    CellLoc = WhatCol & Cnt
                    CellValue = ThisWorkbook.Worksheets(FromWhatSheet).Range(CellLoc).Value
                        If CellValue = Qualif Then
                            nret = Moveit(FromWhatSheet, CellLoc, ToWhatSheet, CutVal)
                        End If
            
            Next
End Function

Function Moveit(FromSheet, WhatRange, ToWhere, CutVal)
    Dim MoveSheetLastRow
            With ThisWorkbook.Worksheets(FromSheet)
            .Select
            .Range(WhatRange).EntireRow.Select
            End With
            
        Selection.Copy
        
        If CutVal = True Then
        Selection.Cut
        End If
        
        MoveSheetLastRow = FindLastRow(ToWhere)
        
            ThisWorkbook.Worksheets(ToWhere).Select
            ThisWorkbook.Worksheets(ToWhere).Cells(MoveSheetLastRow + 1, 1).EntireRow.Select
           
                  
        Selection.Insert
        ThisWorkbook.Worksheets(FromSheet).Select
        Application.CutCopyMode = False
End Function

Function createNew()
Dim NewSheet
        If TabTo = "New Sheet" Then
        ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
        NewSheet = ThisWorkbook.ActiveSheet.Name
        TabTo = NewSheet
        End If
End Function



Sub Button1_Click()
    Dim nret
        buildform
            If FormXcel = False Then
                    If TabFrom <> "" And TabTo <> "" Then
                        createNew
                        nret = LoopForMove(TabFrom, TabTo)
                    Else
                        MsgBox ("Please set a 'From' and a 'To' sheet!")
                    End If
            End If
End Sub

Function Counttabs()
    Counttabs = ThisWorkbook.Worksheets.Count
End Function

Function buildform()
    Dim TabCount
            Controller.ComboBox2.AddItem "New Sheet"
            
            For TabCount = 1 To Counttabs
                Controller.ComboBox1.AddItem ThisWorkbook.Worksheets(TabCount).Name
                Controller.ComboBox2.AddItem ThisWorkbook.Worksheets(TabCount).Name
            Next
    Controller.Show
    
End Function

Thank you to ace3mark for this tip.

Image: © Microsoft.
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team

Related