You are here:

Excel/implementing buttons to transfer data onto a sheet.


Hello Jerry,

I have a spreadsheet with the first page being a works production sheet. Each subsequent sheet contains a product description and list of materials required to build that product. I would like to create a series of buttons on the first page, one button per product, and in selecting the relevant button it takes the materials list from the relevant product description page and copies it into the fields on the works production sheet. The works production sheet can then be printed, cleared and a new works production sheet created in the same fashion using a different button for another product.

Most grateful if you can point me in the right direction how best to achieve this.


Obviously this will need to be adapted in terms of what cells get copied.


1) On your main printable sheet, lets' call it Sheet1, add a FORMS button.  This is the button that has the "assign macro" option when you right-click it

2) Right-click the button and select EDIT TEXT - change the button text to exactly match the name of the sheet you want this button to copy from.

3) Now open the VBEditor and Insert > Module to add a blank module probably Module1 if you're just starting.   

4) Paste this ONE macro into that module, this macro will be used by all the buttons, so you will eventually need to customize the copy/paste commands for you actual need.

Option Explicit

Sub GetDetails()
Dim ws As Worksheet

    Set ws = Sheets(ActiveSheet.Buttons(Application.Caller).Caption)
    ws.UsedRange.Copy Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)

End Sub

5) Close the VBEditor

6) Right-click the button you made earlier and ASSIGN MACRO to attach your button to the GetDetails macro.

7) Duplicate this button and change the button text on each one to exactly match the sheets they should copy from.

It's important the button text be exactly the same text as the sheet names.

That's it.  Now each button you click will copy ALL the used cells from the same-name sheet and paste onto sheet1 in the next empty row down.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jerry Beaucaire


Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques:


Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: ===================== I have been offering free assistance as an Excel aid on many web sites for many years: ( - JBeaucaire) ======== ( - JBeaucaire) ======= ( - jbeaucaire)

Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2017 All rights reserved.