How to auto-populate values from certain cells into a new sheet [Solved/Closed]
Roadknee - - Latest reply:
vcoolio
Moderator Last seen
December 18, 2018
- Aug 12, 2017 at 06:08 AM
- Posts
- 1208
- Registration date
- Thursday July 24, 2014
- Status
Related:
- How to auto-populate values from certain cells into a new sheet
- Excel auto populate cells from another sheet - How-To - Excel
- Auto populate cells in excel based on another cell ✓ - Forum - Excel
- How to auto populate data from multiple sheets to a master ✓ - Forum - Excel
- Auto populate date in excel - How-To - Excel
- How to populate information on multiple sheets at once ✓ - Forum - Excel
6 replies
- Posts
- 1208
- Registration date
- Thursday July 24, 2014
- Status
Hi Rodney,
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.
Thanks Rodney.
Cheerio,
vcoolio.
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.
Thanks Rodney.
Cheerio,
vcoolio.
Figured i'm not allowed to share email addresses on here.
here's the link to the file
https://www.dropbox.com/s/mmq7i029pu2u8yi/Sample%20Invoices%20Workbook.xls?dl=0
Regards,
Rodney
here's the link to the file
https://www.dropbox.com/s/mmq7i029pu2u8yi/Sample%20Invoices%20Workbook.xls?dl=0
Regards,
Rodney
- Posts
- 1208
- Registration date
- Thursday July 24, 2014
- Status
Hi Rodney,
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?
Cheerio,
vcoolio.
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?
Cheerio,
vcoolio.
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.
Regards,
Rodney
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.
Regards,
Rodney
- Posts
- 1208
- Registration date
- Thursday July 24, 2014
- Status
Hello Rodney,
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:
https://www.dropbox.com/s/wffxhasaj4gok6i/Rodney.xlsm?dl=0
Click on the "RUN" button to see it work.
Test the codes in a copy of your work book first.
I hope that this helps.
Cheerio,
vcoolio.
P.S.: Rodney, if the Drop Box link doesn't work, try the following one:-
http://ge.tt/2koRP8m2
I'm having some issues with Drop Box at the moment.
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:-
Sub MoveIt()
Application.ScreenUpdating = False
Dim cArr As Variant, pArr As Variant, X As Long
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lr As Long
Set ws = Worksheets("Invoices")
Set ws1 = Worksheets("Sales Details")
Set ws2 = Worksheets("Helper")
ws2.Visible = False
ws2.UsedRange.ClearContents
cArr = Array("J12", "M12", "C12", "C16", "F15", "B23", "B28", "B33", "B38", "B43", "B48", "C24", "C29", "C34", "C39", "C44", "C49", _
"C25", "C30", "C35", "C40", "C45", "C50", "C26", "C31", "C36", "C41", "C46", "C51", "E23", "E28", "E33", "E38", "E43", "E48", "F23", "F28", _
"F33", "F38", "F43", "F48", "I23", "I28", "I33", "I38", "I43", "I48")
pArr = Array("A2", "B2", "C2", "D2", "E2", "F2", "F3", "F4", "F5", "F6", "F7", "G2", "G3", "G4", "G5", "G6", "G7", "H2", "H3", "H4", "H5", _
"H6", "H7", "I2", "I3", "I4", "I5", "I6", "I7", "J2", "J3", "J4", "J5", "J6", "J7", "K2", "K3", "K4", "K5", "K6", "K7", _
"M2", "M3", "M4", "M5", "M6", "M7")
For X = LBound(cArr) To UBound(cArr)
ws.Range(cArr(X)).Copy
ws2.Range(pArr(X)).PasteSpecial xlPasteValues
Next
lr = ws2.Range("K" & Rows.Count).End(xlUp).Row
ws2.Range("A3:A" & lr) = "=A2"
ws2.Range("B3:B" & lr) = "=B2"
ws2.Range("C3:C" & lr) = "=C2"
ws2.Range("D3:D" & lr) = "=D2"
ws2.Range("E3:E" & lr) = "=E2"
ws2.Range("L2:L" & lr) = "=K2*J2"
ws2.Range("N2:N" & lr) = "=IF(E2=""y"",(L2*(M2*50%)),""0"")"
ws2.Range("O2:O" & lr) = "=N2"
ws2.Range("P2:P" & lr) = "=IF(E2=""n"",(L2*M2),""0"")"
ws2.Range("Q2:Q" & lr) = "=SUM(L2)+SUM(N2:P2)"
ws2.UsedRange.Copy
ws1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
ws1.Columns.AutoFit
ws1.Select
ClearItMergeIt
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Sub ClearItMergeIt()
Dim ws As Worksheet
Set ws = Worksheets("Invoices")
On Error Resume Next
ws.[F15].ClearContents
ws.[J12].ClearContents
ws.[E23:E51].UnMerge: ws.[E23:E51].ClearContents
ws.[I23:I51].UnMerge: ws.[I23:I51].ClearContents
ws.[C16:F16].UnMerge: ws.[C16:F16].ClearContents: ws.[C16:F16].Merge
ws.[C11:F14].UnMerge: ws.[C11:F14].ClearContents
ws.[B23:D23].UnMerge: ws.[B23:D23].SpecialCells(xlCellTypeConstants, 23).ClearContents: ws.[B23:D23].Merge
ws.[C24:D26].UnMerge: ws.[C24:D26].SpecialCells(xlCellTypeConstants, 23).ClearContents
ws.[B28:D28].UnMerge: ws.[B28:D28].SpecialCells(xlCellTypeConstants, 23).ClearContents: ws.[B28:D28].Merge
ws.[C29:D31].UnMerge: ws.[C29:D31].SpecialCells(xlCellTypeConstants, 23).ClearContents
ws.[B33:D33].UnMerge: ws.[B33:D33].SpecialCells(xlCellTypeConstants, 23).ClearContents: ws.[B33:D33].Merge
ws.[C34:D36].UnMerge: ws.[C34:D36].SpecialCells(xlCellTypeConstants, 23).ClearContents
ws.[B38:D38].UnMerge: ws.[B38:D38].SpecialCells(xlCellTypeConstants, 23).ClearContents: ws.[B38:D38].Merge
ws.[C39:D41].UnMerge: ws.[C39:D41].SpecialCells(xlCellTypeConstants, 23).ClearContents
ws.[B43:D43].UnMerge: ws.[B43:D43].SpecialCells(xlCellTypeConstants, 23).ClearContents: ws.[B43:D43].Merge
ws.[C44:D46].UnMerge: ws.[C44:D46].SpecialCells(xlCellTypeConstants, 23).ClearContents
ws.[B48:D48].UnMerge: ws.[B48:D48].SpecialCells(xlCellTypeConstants, 23).ClearContents: ws.[B48:D48].Merge
ws.[C49:D51].UnMerge: ws.[C49:D51].SpecialCells(xlCellTypeConstants, 23).ClearContents
ws.[C24:D24].Merge: ws.[C25:D25].Merge: ws.[C26:D26].Merge
ws.[C29:D29].Merge: ws.[C30:D30].Merge: ws.[C31:D31].Merge
ws.[C34:D34].Merge: ws.[C35:D35].Merge: ws.[C36:D36].Merge
ws.[C39:D39].Merge: ws.[C40:D40].Merge: ws.[C41:D41].Merge
ws.[C44:D44].Merge: ws.[C45:D45].Merge: ws.[C46:D46].Merge
ws.[C49:D49].Merge: ws.[C50:D50].Merge: ws.[C51:D51].Merge
ws.[E23:E26].Merge: ws.[E28:E31].Merge: ws.[E33:E36].Merge: ws.[E38:E41].Merge: ws.[E43:E46].Merge: ws.[E48:E51].Merge
ws.[F23:F26].Merge: ws.[F28:F31].Merge: ws.[F33:F36].Merge: ws.[F38:F41].Merge: ws.[F43:F46].Merge: ws.[F48:F51].Merge
ws.[G23:G26].Merge: ws.[G28:G31].Merge: ws.[G33:G36].Merge: ws.[G38:G41].Merge: ws.[G43:G46].Merge: ws.[G48:G51].Merge
ws.[H23:H26].Merge: ws.[H28:H31].Merge: ws.[H33:H36].Merge: ws.[H38:H41].Merge: ws.[H43:H46].Merge: ws.[H48:H51].Merge
ws.[I23:I26].Merge: ws.[I28:I31].Merge: ws.[I33:I36].Merge: ws.[I38:I41].Merge: ws.[I43:I46].Merge: ws.[I48:I51].Merge
ws.[J23:J26].Merge: ws.[J28:J31].Merge: ws.[J33:J36].Merge: ws.[J38:J41].Merge: ws.[J43:J46].Merge: ws.[J48:J51].Merge
ws.[K23:K26].Merge: ws.[K28:K31].Merge: ws.[K33:K36].Merge: ws.[K38:K41].Merge: ws.[K43:K46].Merge: ws.[K48:K51].Merge
ws.[L23:L26].Merge: ws.[L28:L31].Merge: ws.[L33:L36].Merge: ws.[L38:L41].Merge: ws.[L43:L46].Merge: ws.[L48:L51].Merge
ws.[M23:M26].Merge: ws.[M28:M31].Merge: ws.[M33:M36].Merge: ws.[M38:M41].Merge: ws.[M43:M46].Merge: ws.[M48:M51].Merge
ws.[C11:F11].Merge: ws.[C12:F12].Merge: ws.[C13:F13].Merge: ws.[C14:F14].Merge
End Sub
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:
https://www.dropbox.com/s/wffxhasaj4gok6i/Rodney.xlsm?dl=0
Click on the "RUN" button to see it work.
Test the codes in a copy of your work book first.
I hope that this helps.
Cheerio,
vcoolio.
P.S.: Rodney, if the Drop Box link doesn't work, try the following one:-
http://ge.tt/2koRP8m2
I'm having some issues with Drop Box at the moment.
Hey VCOOLIO!,
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!
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!
- Posts
- 1208
- Registration date
- Thursday July 24, 2014
- Status
Hello Rodney,
Excellent! I'm glad to have been able to help.
Yes. Just alter the arrays as needed and keep them ordered but, like I said, just come back for help if you get stuck somewhere along the line.
Did the other link work?
I'll leave it to you to mark this thread as solved.
Cheerio,
vcoolio.
Excellent! I'm glad to have been able to help.
Yes. Just alter the arrays as needed and keep them ordered but, like I said, just come back for help if you get stuck somewhere along the line.
Did the other link work?
I'll leave it to you to mark this thread as solved.
Cheerio,
vcoolio.
- Posts
- 10949
- Registration date
- Monday June 3, 2013
- Status
Holy cow, That is a lot of footwork there, bud! Nice job, once again!
I am saving this link as a proof of concept, so others can use the model!
I think Rodney owes you a steak (I have to keep with the beef theme!)!
I am saving this link as a proof of concept, so others can use the model!
I think Rodney owes you a steak (I have to keep with the beef theme!)!
- Posts
- 1208
- Registration date
- Thursday July 24, 2014
- Status
- Posts
- 10949
- Registration date
- Monday June 3, 2013
- Status
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).
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..................
Cheerio,
vcoolio.
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..................
Cheerio,
vcoolio.
- Posts
- 1208
- Registration date
- Thursday July 24, 2014
- Status
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! :)
And yes. The other link worked too.!
The effort is really commendable mate. I even showed it to my coworkers as proof that there are some genuinely helpful people out here! :)
And yes. The other link worked too.!
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