How to Auto-populate Values from certain cells into a New Sheet [Solved]
Roadknee -
Aug 10, 2017 at 01:51 AM - Latest reply: vcoolio1171
Posts Thursday July 24, 2014Registration dateModeratorStatus
June 21, 2018
Last seen
- Aug 12, 2017 at 06:08 AM
Hope everyones doing okay. I've been working on a billing sheet for a company with multiple product codes and rates etc to be pulled from the master sheet. I would like to create a macro wherein values from certain cells such as invoice number, date, client name, part name, rate, units, taxable values, different taxes, and the total amount to be auto populated into a different sheet, thus saving the task of having to fill the vey same values for the purpose of maintaining records.
Any help would be really appreciated.
Thanks very much in advance.!
Hey Vcoolio, thank for getting back.
No. I meant I wanted to create one new sheet. From the the calculation and invoice sheet named 'Invoices', I want data to be populated in say 'Sales Details' , where in there is a serial invoice wise entry along the values from fields I had mentioned of all the invoices I created using the 'Invoices' sheet
OK. Can you now supply us with an example so that we can better understand your scenario.
Upload a sample of your work book (please use dummy data) to a free file sharing site such as Drop Box and then post the link to your file back here.
This will give us something to work with and test. Please show us in the sample your inputs ("Invoices" sheet) and the expected results in the "Sales Details" sheet.
Thank you so much for coming forward to help. My mail is ***@*** . If you could send me your contact mail on my address, id be happy to share the file for you to take a look at.
Good that you picked up on that. This is a public forum and, for the benefit of anyone else who comes this way looking for a similar solution, all queries need to be dealt with publicly not privately.
Anyway, I'm assuming that your invoice set up will remain the same for all clients and you would like the details from each section of the invoice summarised in the Sales Details sheet, listed horizontally under the appropriate heading. The Sales Details sheet will basically be a summary sheet for all client invoices. Have I understood this correctly?
If so, I'll have a look at this tonight and get back to you tomorrow.
BTW, I also assume that you would like the entries in the invoice to be cleared once transferred to the Sales Details sheet. Is this correct?
Hey mate,
Thank you so much for helping me out with this.
You are correct, the invoice set up will remain the same and values generated therein will be listed horizontally under their respective headings. ( I have tried to color code them for reference.)
The sales data sheet would be a summary of values generated in the invoice and would be best if I could clear the key values from the invoice sheet barring the formulae once the data is populated in the sales data sheet.
Also, it would be great if you could provide some pointers on how and where the macro formula can be edited in the future if certain cell values are to be removed, added or modified.
Really appreciate this help. Thanks again.
Well, that was a bit more involved than I thought it would be! It was a bit tricky because of how the invoice is set up. However, following are the codes that should cover all you need:-
Merged cells generally play havoc with VBA coding but I think that I've managed to get around that problem. It would be good if you could change the formatting to exclude any merged cells as this would also reduce the amount of code required for a work around.
There are two codes above but place them in one standard module and assign the "MoveIt" code to a button in your "Invoices" sheet.
Because of the way the invoice is set up, I found it simpler to use a helper sheet, so, in your work book, open a new sheet and name it "Helper". Don't worry about placing any headings in the "Helper" sheet. The code will actually hide this sheet once it has done its job.
You'll note in the code that I have used your formulae (slightly modified) to help with the data transfer.
The key values are cleared from the invoice once the data transfer is completed with all formulae being left intact.
In the date cell (M12), place the following formula:-
=Today()
This will automatically click the date over for you each day.
Should you ever need to, the key to making any alterations to the code lie in the copy array (cArray) and the paste array (pArray). But be careful with altering anything as both arrays are tied to each other for correct copying/pasting. For example, J12 (Invoices) is pasted to A2 (Sales Details), M12 is pasted to B2 etc.. They are ordered pairs and must be in the same position in their respective arrays. You may also need to carefully examine the second part of the code ("ClearItMergeIt) to ensure correct unmerging-clearing-merging. But, if you are not too sure, just come back here for help.
Following is the link to your work book with the code implemented:
WOW!! Works like a charm! Does exactly what i had set out to! Thank you so very much for your tremendous help! I was not able to access the drop box file, but i managed to insert the code and get it to run!. I really appreciate you continuing to be involved!
So just to be clear, henceforth if i have to add new cell values or remove existing ones, i just enter/delete the cell number from the (cArray) and ensure that the corresponding cell number is entered/deleted from the (pArray)?
Once again, really grateful for the effort!
vcoolio1171
Posts Thursday July 24, 2014Registration dateModeratorStatus
June 21, 2018
Last seen
>
ac3mark9622
Posts Monday June 3, 2013Registration dateModeratorStatus
June 22, 2018
Last seen -Aug 11, 2017 at 08:05 PM
Hi Mark,
Thanks for the kind words. It became a bit of a challenge so I just "kept on truckin' "!
The merged cells provided the challenge (don't like merged cells).
I am saving this link as a proof of concept, so others can use the model!
Yes. Good idea. After all, that's what these public forums are for.
Ha. The beef theme. I like that and I do like a steak. Rodney..................
Ha. For all that work , I need to buy you a couple of pints to wash down that steak as well bud!
The effort is really commendable mate. I even showed it to my coworkers as proof that there are some genuinely helpful people out here! :)
vcoolio1171
Posts Thursday July 24, 2014Registration dateModeratorStatus
June 21, 2018
Last seen
>
ac3mark9622
Posts Monday June 3, 2013Registration dateModeratorStatus
June 22, 2018
Last seen -Aug 12, 2017 at 06:02 AM
No. I meant I wanted to create one new sheet. From the the calculation and invoice sheet named 'Invoices', I want data to be populated in say 'Sales Details' , where in there is a serial invoice wise entry along the values from fields I had mentioned of all the invoices I created using the 'Invoices' sheet
Regards,
Rodney