I'm never done a macro before and of course I'm very new to excel. I have a worksheet for lessons learned that have multiple sheets for each state. I want to create a macro that every time I enter a new data into a row and hit "Add to summary sheet" and it copy and paste the the next available row in summary sheet. I"m very new to excel , so please help me with the code.
Please see the attached file for my workbook. Thank you guys
From a "process" point of view, I'd think pressing buttons on each sheet to transfer data to the Summary would quickly lead to duplications and errors. I would think it would be better to have the SUMMARY sheet collect it's own data for you fresh each time you look at the Summary sheet. This way you can make edits and updates to the existing data already on each of the individual sheets, and the next time you look at the SUMMARY those updates will be there as well.
We would create a Worksheet_Activate macro in the SUMMARY sheet module that triggers itself each time you view the sheet. It would clear it's current data, then run through all the sheets and copy the data in anew, skipping the sheets "Sheet1" and "Summary" as it copies.
Would that work for you? No buttons!
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Sounds great. Would please help me with the code. Thank you JBeaucaire for very quick respond
This is the event macro in the Summary sheet module:
I also cleaned up a lot of stray garbage down below on each of your sub sheets. Make sure there isn't any of the in your real workbook or it may mess up the macro's attempt to see the last row of data on each sheet and copy down to that point only.
Please Login or Register to view this content.
Great. Thank you so much JBeaucaire.
I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.
Got it. Thanks JBeaucaire
JBeaucaire
I noticed if I enter the data in order sheet1, sheet2, sheet3 then this code works, but I enter data from sheet 4 first, it would copy all the header from sheeet 1, sheet2, sheet3. I don't want to copy the header just the data (A9). Please advise
Thank you
Blanks, didn't think about that. Here's a tweak:
Please Login or Register to view this content.
Yay, it works
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks