Excel/implementing buttons to transfer data onto a sheet.
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.
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)
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.
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