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
The UserForm
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, copy the following example, taking care to name each control correctly:
Attaching Code to Each Control
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:
The Code
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
Photo: 123rf.com