Excel Button to copy changes to specific cells on new sheet

Copper Contributor

Hello, I need help, I am trying to make a new excel file that will copy any changes that are made to a new excel sheet as part of that history and also so I can print the changes off. Not sure if you have to save changes first before button click but I don't mind either way. For instance, 


I have an excel file that tracks new cars coming in to a specific lot. So for the new car it would have it's own excel sheet and sections on each like this:


Lot Section where you choose which lot it is in (either check mark or X):

Lot A
Lot B

Lot C 


Then there would be another section for color of the car with either a check mark or X:





And at the bottom of the sheet I want there to be a button that when it's clicked, it will copy the changes. So if a new car comes in to lot C and it's red, I would like to be able to click the button and it takes those changes to a specific area on a new sheet or even if it's possible to move it to an existing sheet called ChangeLog.


I would have text on the existing sheet (or new if existing can't be done) that says : 

"Lot car was placed in:" and "Color of new car:" and the data would be pasted below those sections. Can someone help please? 


4 Replies



I think that you may be approaching this in the wrong way, frankly.


In general, creating a separate sheet for each new entity--in your case, cars--is a mistake. Your workbook would be far more likely to be effective and manageable if you set up a single inventory sheet that records the locations and color (and model, year, other features) of each car on the same sheet. Excel then can be used with a LOT of power, to extract all of that based on any of those identifying characteristics.


Separating the raw data--the single database with all of those cars--from the output is a far better way to design an inventory management system. It sounds like you may already have a sheet that tracks each new car coming in. I suggest you focus on making that a solid and robust core to your system. Then develop the ways to extract a record ( or multiple records) of cars, their locations and features, that might meet the criteria of a customer.


If you have the newest version of Excel, I suspect you'd find the FILTER function very useful. Here's a very helpful introductory video on that and some associated functions. https://www.youtube.com/watch?v=9I9DtFOVPIg

But by all means, come back with more questions. If you do in fact have a working spreadsheet already that could serve as the core database, please attach a copy (so long as no proprietary info is included).

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, but were limited to power users. With Dynamic Arrays we have rebuilt the calc engine, effectively turning all formulas into array formulas ...

@mathetes I guess I probably used bad examples, I'm not actually using cars or lots or colors. I'm using this as a System Access Authorization form that stores logs when changes are made to specific users access. The first sheet will have all of the current Users system data, the sheets after would be change logs made to the first sheet and would be dated. The change logs will be printed off and have places for signatures authorizing the changes being made. 


I just need help creating the button that would copy changes made to specific areas on another sheet in the same workbook so that they can be printed off and "authorized". I just wanted to keep it simple and use any examples given to design it specifically for what I need. 

I will maintain still that a single transaction history (which can be used to record changes) is a better approach. I base that on years of experience as the director of a major database design (for HR and Payroll for a major US corporation)......

Database Design is my purview; Excel buttons that do things are not.....so I'll leave that to others. But I do suggest you think about your design.
It may be a better approach but I'm not trying to achieve transaction history as my main goal. My main goal is to have something to print off after making changes to a user without having to design a database or go too indepth. It will not be used a lot overall, it's primary purpose will be to print changes made and get signatures on them. The log part is just a nice byproduct of that since with my design you'll have a new sheet, so I figured why not give it a date and you're good to go.