Data Transfer Between Sheets/Also using "Find the Last Row"
I'm using a form in Excel that gets reset every time and re-used over and over again. It's a Special Order Form, and I already figured out how to get an ascending order number generator to work in it to generate a Special Order Number. The cell the number generation occurs in is "D2". There are many different fields on this order form that I would like to get transferred to the 2nd sheet which I've named "TRACKING", to be used for order tracking purposes. The items tracked would be arranged in rows, and each time I would save the form, I would want the data to automatically populate the correct fields on the 2nd Sheet on the correct row. So this would be triggered by my using the "Save" function under the "File" drop-down. At the beginning of this macro, it should "Find the Last Row", so as not to overwrite the previous tracking information. How would I make sure that this macro will not over-write previous rows?
Thanks for any help!
Hi Cakalic, In your macro add
Hi Cakalic,
In your macro add the following line:
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Where "A" is the column you wish it to search for the last row on.
Then continue to paste the data that has been copied
Regards
Mark
Hi Mark, Thanks for your
Hi Mark,
Thanks for your input. However, I'm trying to avoid pasting data. I want the form on Sheet 1 to auto-populate the correct fields on the correct rows in Sheet 2 every time I save the document in Sheet 1. Is there a way to get saving the document in Sheet 1 to be a macro trigger?
Thanks,
Luke
As far as I'm aware and I
As far as I'm aware and I could be very wrong here, you can't attach a macro to the save function.
You can add the macro to your your ribbon in excel but that would be only in your excel and not another users, File - Options - Customize ribbon - Choose commands from = Macro - then add to wherever you wish it to be on your ribbon.
Alternatively and probably more useful you could use a button on the spreadsheet by going to developer - insert - Form controls or Active X controls - button (top left icon in both fields)
If you are not used to using buttons best use Form control rather than Active X as you can easily assign a macro to the button by right clicking and selecting assign macro
Hope this was all useful
Mark
Thank you. Yes this was very
Thank you. Yes this was very useful. My boss said the same thing. So, I will try to assign it to a macro button in the form.One other problem I'm having is that the form has some areas that are vertical where the text is written horizontally but still vertically at the same time of that makes any sense. When I try to get that to copy and paste, it just blankly fills in the rows from the original "vertical (column). The text is in a horizontal arrangement, but that arrangement is turned into a vertical position of that makes any sense. Thank you for your help. Should I send you pics of both forms I'm trying to work with? Maybe if you see it you wool be able to help more. Once again, thank you for all of your help!;-)
Sounds like it's transposing
Sounds like it's transposing the data, prob best if you did attach an example