Previous Topic Next Topic

How to auto-populate values from certain cells into a new sheet

Solved/Closed
Roadknee - Updated on Nov 12, 2018 at 12:45 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Aug 12, 2017 at 06:08 AM
Hello guys,

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.!

Regards,
Rodney



Related:

6 responses

Answer 1 / 6
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Aug 10, 2017 at 02:11 AM
Hello Rodney,

Do you mean that you would like to populate a number of different sheets? That is, a different sheet for each customer?

Cheerio,
vcoolio.
0
Roadknee
Aug 10, 2017 at 02:42 AM
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

Regards,
Rodney
0
Answer 2 / 6
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Aug 10, 2017 at 02:55 AM
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.
0
Roadknee
Aug 10, 2017 at 05:40 AM
Hey Vcoolio,

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.

Regards,
Rodney
0
Roadknee
Aug 10, 2017 at 05:44 AM
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
0
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259 > Roadknee
Aug 10, 2017 at 06:14 AM
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.
0
Roadknee
Aug 10, 2017 at 02:21 PM
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
0
Answer 3 / 6
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Updated on Aug 11, 2017 at 07:15 AM
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:- 

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.
0
Roadknee
Aug 11, 2017 at 09:37 AM
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!
0
Answer 4 / 6
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Aug 11, 2017 at 07:34 PM
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.
0
Blocked Profile
Aug 11, 2017 at 07:43 PM
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!)!
0
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259 > Blocked Profile
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..................

Cheerio,
vcoolio.
0
Roadknee > vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023
Updated on Aug 11, 2017 at 10:50 PM
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.!
0
Blocked Profile
Aug 11, 2017 at 08:48 PM
I deleted the line about giving the milk away for free! LOL :-P
0
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259 > Blocked Profile
Aug 12, 2017 at 06:02 AM
Hmmmm... I think I know what you mean!
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 6
Blocked Profile
Aug 11, 2017 at 10:54 PM
@rodney, keep coming back, we will all learn with contributors such as vcoolio!
0
Roadknee
Aug 11, 2017 at 11:00 PM
Absolutely. Will always keep coming back whenever there is a particularly tricky subject!
0
Answer 6 / 6
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Aug 12, 2017 at 06:08 AM
Gentlemen,

Great positive attitudes.

Thanks Rodney and Mark for all the kind words. All the Volunteers here do what they can, when they can, to help out.

We might see you again Rodney (steak and pints in hand!).

Cheerio,
vcoolio.
0

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
Any auto clicker for Xbox One?
vic11con -
julietdcosta -

13 responses