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.

Answer:

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

Related posts:

Basic data entry (vba)

Create links to all sheets in a workbook

List all open workbooks and corresponding sheets (vba)

Add values to a table (vba)

Quickly filter a column in an excel table