Transferring data to 2 different sheets from one main data entry sheet

[cjarollins]

I have constructed a main data sheet where we can enter data for Return Good and Credit Memo. I'd like to have the info we enter to be transferred to the RGA sheet and the Credit Memo sheet. Then, I would like the sheet set up to where after we enter the data, we can "press" a macro button? and have both the RGA sheet and the Credit Memo sheet to save. Also, after hitting the button to create the two other sheets and save, if the Main Data Sheet can reset to empty and be ready for the next entry. The other issue would be if we have more than 1 item to enter (up to 9 items), how would all that info be transferred to the corresponding sheet? A little confusing but I can answer any questions you might have.
Thank you!

 

[mumps]

Please have a look at Post #7 .

 

[cjarollins]

Ok, again being ignorant, do I need to copy and paste then hit F5 everytime?? Or can something a little more easier be set up?

 

[mumps]

In order to run the macro, you must first copy/paste it as I described in Post #7 . Once it is there, each time you want to run it manually, you would have to open the VBA Editor and press F5. This is not practical so the best way to run it would be to insert a button on your sheet and assign the macro to it. Then all you would have to do is click the button. Alternatively, you could assign the macro to a short cut key. Give me a few minutes and I will attach your file with the button.

 

[mumps]

Click here to download your file. After you open it, first save it to the folder of your choice and then click the button in the upper left hand corner of sheet "RGA-Credit Info". The saved file should be in the same folder where you saved your main file. You will notice that sheet "RGA-Credit Info" has been cleared. Just a reminder that if you also want the other 2 sheets to be cleared as well, as I think they should, please let me know and I will modify the macro.

 

[cjarollins]

Ok, it's working perfectly. Yes, clearing the two sheets would be excellent after hitting the button to create.
Thank you!

 

[mumps]

Click here for the revised file. Please note that I have had to unmerge the cells in the RGA Return and Credit Memo sheets as the merged cells were causing problems.

 

[cjarollins]

Thank you for all your help. When the files are saving, both sheets are saving to the same file. I cannot have both sheets saved to the same file. One sheet goes to the distributor and the other to the dealer. The dealer cannot have the credit sheet with the pricing on it. If both sheets are saved to the same file and someone forgets to go in and remove that info on the credit sheet or remove the credit sheet altogether, then the customer has the distributor pricing which will be no good at all. So, I need the sheets to save to separate locations. Also, the AMOUNT TO CREDIT SECTION needs to calculate all quantities multiplied by the price of each item added up and then multiplied by the marketing allowance percentage and then the total to refund minus the marketing allowance total for a final credit total. I hope this makes sense.

 

[mumps]

Could you describe what the AMOUNT TO CREDIT SECTION needs to calculate by referring to the cells and ranges? For example, which cells/ranges need to be multiplied, added, subtracted. etc.

 

[cjarollins]

Amount to Credit is cell D8 on the Credit Memo Sheet. Amount to credit is determined by multiplying cell (A10 * B10)+ (A11 * B11) + etc...according to how many total line items you have. This total is then multiplied by the marketing Allowance percentage (Cell B21 on the Credit Memo Sheet)....let's say 4%....So, to summarize, the total of all "A" cells * the corresponding "B" cells less the marketing allowance percentage, in this case less 4%, would be the Amount that is in cell D8.

 

[mumps]

Here is your file. Give it a try. It will save the two sheets as separate files and also calculate the Amount to Credit.