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.
Macro category
This article demonstrates how to add or remove a value in a regular drop down list based on a list […]
In this tutorial, I am going to show you how to add values to a drop down list programmatically in […]
This article demonstrates how to place values automatically to a table based on two conditions using a short macro. Cell […]
Excel categories
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 pressed on. 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 pressed 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.