Add values to worksheets based on a condition [VBA]
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
- Select cell E2.
- Go to tab "Data" on the ribbon.
- Press with left mouse button on "Data Validation" button.
- Go to "Settings" tab.
- Select List
- Type 2011, 2012 (sheet names) in Source:
- 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?
- Copy code below.
- Go to Excel and press Alt+ F11 to open the VB Editor (Visual Basic Editor).
- Select your workbook in the Project Explorer.
- Press with left mouse button on Insert on the menu.
- Press with left mouse button on Module to insert a module to the selected workbook.
- Paste macro code to the code module.
- 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
- Go to "Developer" tab on the ribbon.
- Press with left mouse button on "Insert" button.
- Press with left mouse button on "Button" (Form Control).
- Press with left mouse button on and drag on worksheet to create button.
- Assign macro AddValues
- Press with left mouse button on OK button.
Press with left mouse button on the button to run macro AddValues.
Weekly Blog EMAIL
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
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
Run a Macro from a Drop Down list [VBA]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
7 Responses to “Add values to worksheets based on a condition [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Can you provide the code for Add values to different sheets (vba)
vivek,
It is already on this webpage, below the heading Macro code.
Here it is again:
Sub AddValues() Dim i As Single i = Worksheets("" & Range("D2")).Range("A" & Rows.Count).End(xlUp).Row + 1 Worksheets("" & Range("D2")).Range("A" & i & ":C" & i) = _ Worksheets("Enter Data").Range("A2:C2").Value Worksheets("Enter Data").Range("A2:C2") = "" End Subis it possible for add values to different workbooks like add values to different sheets (vba)? can you send me the example file. thanks.
[…] are great for doing repetitive tasks. Two years ago I wrote a post about transferring data to worksheets. It is about automatically moving data to a worksheet you […]
would love to see your post about transferring data to worksheets! I would like to parse out a huge data file into seperate tabs, automatically.
Thank you for this. I am new to Excel and I have created a workbook that has no macros so far but I am looking for something very similar to this vba code and especially like how you included the dates and how the code gets copied to new rows on the year sheet each time the "Add" button is clicked. I am wondering how to change the code slightly for the following application: Is there a way to copy different cells (not a range) in a single column ie: A2,A3,A4,A8,A10 on the "Enter Data" sheet to the year sheet in columns (ie: D3,D4,D5,D6,D7) rather than rows? Also, I see after the "Add" button is clicked it removes the values from the cells. Can the code be updated to not clear the cells? Thanks ny information you can provide.
Oops, I made 1 mistake in my last comment. I would like to copy a date from f12 along with data from A2,A3,A4,A8,A10 but want the date transferred to the year sheet in the same column as the date D3(Date),D4,D5,D6,D7,D8.