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