This tutorial shows you how to add a record to a particular worksheet based on a condition, the image above shows input fields name, country, company and sheet.

The sheet input field determines which worksheet to add the values to, the add button allows you to run the macro and place the values automatically.

I will also show you the macro code to accomplish this task, what each line does, and where to put the code.

Check this article out Split data across multiple sheets VBA if you have a list you want to move to worksheets based on a condition automatically.

This article is inspired by this question:

The animated image above demonstrates how to type input values and then cut values and paste to the given worksheet.

To make things easier a drop down list lets the user pick worksheet name in cell D2.

How to create a drop down list

1. Select cell E2.
2. Go to tab "Data" on the ribbon.
3. Press with left mouse button on "Data Validation" button.
4. Go to "Settings" tab.
5. Select List
6. Type 2011, 2012 (sheet names) in Source:
7. Press with left mouse button on OK

A black arrow appears next to cell E2, see image above.  Press with left mouse button on the arrow to expand a list that allows you to select a worksheet name.

Where to put the code?

1. Copy code below.
2. Go to Excel and press Alt+ F11 to open the VB Editor (Visual Basic Editor).
3. Select your workbook in the Project Explorer.
4. Press with left mouse button on Insert on the menu.
5. Press with left mouse button on Module to insert a module to the selected workbook.
6. Paste macro code to the code module.
7. Exit VB Editor.

VBA code

'Name macro
Sub AddValues()

'Dimension variable and declare data type
Dim i As Single

'Save row number of cell below last nonempty cell
i = Worksheets("" & Range("E3")).Range("A" & Rows.Count).End(xlUp).Row + 1

'Save input values to selected worksheet
Worksheets("" & Range("E3")).Range("A" & i & ":C" & i) = _
Worksheets("Enter Data").Range("B3:D3").Value

'Clear input cells
Worksheets("Enter Data").Range("A2:C2") = ""

'Stop macro
End Sub

Create a press with left mouse button onable button that runs a macro

1. Go to "Developer" tab on the ribbon.
2. Press with left mouse button on "Insert" button.
3. Press with left mouse button on "Button" (Form Control).
4. Press with left mouse button on and drag on worksheet to create button.
5. Assign macro AddValues
   
6. Press with left mouse button on OK button.

Press with left mouse button on the button to run macro AddValues.

Get the Excel file

Add-values-to-different-sheetsv2.xlsm

---

Weekly Blog EMAIL

[newsletter_signup_form id=1]

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

---

List files in folder and create hyperlinks (VBA)

Table of Contents List files in a folder and create hyperlinks (VBA) How to navigate quickly in a complex workbook […]

Lookup with any number of criteria

This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]

Unique distinct records sorted based on count or frequency

This article demonstrates how to sort a table based on count meaning the formula counts each record and returns a […]

Find cells containing formulas with literal (hardcoded) values

This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]

Filter values that exists in all three columns

This article explains how to extract values that exist in three different columns, they must occur in each of the […]

Basic data entry [VBA]

In this small tutorial, I am going to show you how to create basic data entry with a small amount […]