Author: Oscar Cronquist Article last updated on January 09, 2018

Phil asks:

Could you please show me the code to place the copied data into a different tab instead of below the input cells. Its annoying me and I'm rubbish at this. thanks.


Create drop down list

  1. Go to tab "Data"
  2. Click "Data Validation" button
  3. Go to "Settings" tab
  4. Select List
  5. Type 2011, 2012 (sheet names) in Source:
  6. Click OK

Macro code

  1. Press Alt+ F11
  2. Right click your workbook in project explorer
  3. Click Insert
  4. Click Module
  5. Paste macro code below to module
  6. Exit VB Editor
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 Sub

Create button

  1. Go to "Developer" tab
  2. Click "Insert" button
  3. Click "Button" (form control)
  4. Assign macro AddValues()
  5. Click OK

Download excel file *.xlsm

Add values to different sheets.xlsm