How to auto-populate values from certain cells into a new sheetSolved/Closed

Question

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



System Configuration: Windows / Chrome 60.0.3112.90

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

ac3mark · Answer

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

vcoolio · Answer

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.

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

6 responses

Similar discussions

Subscribe To Our Newsletter!