Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

How to auto-populate values from certain cells into a new sheet [Solved/Closed]

Report
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 -
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



See more

6 replies

Answer 1 / 6
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 212
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.
Report

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
Answer 2 / 6
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 212
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.
Report

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
Report

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
Report
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 212 > Roadknee
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.
Report

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
Answer 3 / 6
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 212
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.
Report

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!
Answer 4 / 6
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 212
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.
Report
Blocked Profile
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!)!
Report
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 212 > Blocked Profile
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.
Report
>
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
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.!
Report
Blocked Profile
I deleted the line about giving the milk away for free! LOL :-P
Report
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 212 > Blocked Profile
Hmmmm... I think I know what you mean!
Answer 5 / 6

@rodney, keep coming back, we will all learn with contributors such as vcoolio!
Report

Absolutely. Will always keep coming back whenever there is a particularly tricky subject!
Answer 6 / 6
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 212
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.
Ask a question