Author: Oscar Cronquist Article last updated on August 15, 2019

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:

Phil asks: Could you please show me the code to place the copied data into a different tab instead of below the input cells. thanks.

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. Click "Data Validation" button.
  4. Go to "Settings" tab.
  5. Select List
  6. Type 2011, 2012 (sheet names) in Source:
  7. Click OK

A black arrow appears next to cell E2, see image above.  Click 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. Click Insert on the menu.
  5. Click Module to insert a module to the selected workbook.
  6. Paste macro code to the code module.
  7. Exit VB Editor.
Note, make sure you save the workbook with file extension *.xlsm (macro-enabled).

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 clickable button that runs a macro

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

Click the button to run macro AddValues.

Download Excel file


* You will also get a weekly newsletter, unsubscribe anytime!