hi
this is the first post . I hope some help by vba actually I have no any experience.
I have some data in sheet1 . it represents data of invoice should copy specific cells (D2,C6,E6) repeatedly in sheet2 based on copied range from b10 to lastrow (G14) but be carefully I said lastrow this doesn't mean the end G14 it depends on how many items should fill may be G15 OR G16 and so on , then I insert new rows what I need it . also copy range from B10 :G14
note: RANGE(B10 :G14 & G17) are changable in location. I mean changing in last row in col G whether (GOODS,TYPE,PR) or ( NET ) and when copy the value is existed in , also should show G17 in the second row for each invoice and when copy another data of invoice should create a new header to break among the invoices
expected result
this is the first post . I hope some help by vba actually I have no any experience.
I have some data in sheet1 . it represents data of invoice should copy specific cells (D2,C6,E6) repeatedly in sheet2 based on copied range from b10 to lastrow (G14) but be carefully I said lastrow this doesn't mean the end G14 it depends on how many items should fill may be G15 OR G16 and so on , then I insert new rows what I need it . also copy range from B10 :G14
note: RANGE(B10 :G14 & G17) are changable in location. I mean changing in last row in col G whether (GOODS,TYPE,PR) or ( NET ) and when copy the value is existed in , also should show G17 in the second row for each invoice and when copy another data of invoice should create a new header to break among the invoices
INVI.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | INVOICE NO | ||||||||
2 | FR-1000 | ||||||||
3 | |||||||||
4 | |||||||||
5 | ORDER NO : | DATE | |||||||
6 | AS-1000 | 12/07/2021 | |||||||
7 | |||||||||
8 | |||||||||
9 | ITEM | GOODS | TYPE | PR | PRICE | QTY | TOTAL | ||
10 | 1 | FD-122 | ASDQW1 | NN1 | 20.00 | 13.00 | 260.00 | ||
11 | 2 | FD-123 | ASDQW2 | NN2 | 15.00 | 250.00 | 3,750.00 | ||
12 | 3 | FD-124 | ASDQW3 | NN3 | 12.00 | 50.00 | 600.00 | ||
13 | 4 | FD-125 | ASDQW4 | NN4 | 20.00 | 60.00 | 1,200.00 | ||
14 | 5 | FD-126 | ASDQW5 | NN5 | 25.00 | 80.00 | 2,000.00 | ||
15 | TOTAL | SUBTOTAL | 7810 | ||||||
16 | DISCOUNT | 12 | |||||||
17 | NET | 7798 | |||||||
18 | |||||||||
sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G10:G14 | G10 | =E10*F10 |
G15 | G15 | =SUM(G10:G14) |
G17 | G17 | =G15-G16 |
expected result
INVI.xlsm | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | ITEM | DATE | INVOICE NO | ORDER NO | GOODS | TYPE | PR | PRICE | QTY | TOTAL | NET | ||
2 | 1 | 12/07/2021 | FR-1000 | AS-1000 | FD-122 | ASDQW1 | NN1 | 20.00 | 13.00 | 260.00 | 7,798.00 | ||
3 | 2 | 12/07/2021 | FR-1000 | AS-1000 | FD-123 | ASDQW2 | NN2 | 15.00 | 250.00 | 3,750.00 | |||
4 | 3 | 12/07/2021 | FR-1000 | AS-1000 | FD-124 | ASDQW3 | NN3 | 12.00 | 50.00 | 600.00 | |||
5 | 4 | 12/07/2021 | FR-1000 | AS-1000 | FD-125 | ASDQW4 | NN4 | 20.00 | 60.00 | 1,200.00 | |||
6 | 5 | 12/07/2021 | FR-1000 | AS-1000 | FD-126 | ASDQW5 | NN5 | 25.00 | 80.00 | 2,000.00 | |||
7 | ITEM | DATE | INVOICE NO | ORDER NO | GOODS | TYPE | PR | PRICE | QTY | TOTAL | NET | ||
8 | 1 | 12/07/2021 | FR-1001 | AS-1001 | FD-127 | ASDQW6 | NN6 | 10.00 | 50.00 | 500.00 | 790 | ||
9 | 2 | 12/07/2021 | FR-1001 | AS-1001 | FD-128 | ASDQW7 | NN7 | 10.00 | 20.00 | 200.00 | |||
10 | 3 | 12/07/2021 | FR-1001 | AS-1001 | FD-129 | ASDQW8 | NN8 | 10.00 | 10.00 | 100.00 | |||
11 | |||||||||||||
RESULT |