copy information invoice from sheet to another sheet

Mussa

Active Member
Joined
Jul 12, 2021
Messages
264
Office Version
  1. 2019
  2. 2010
hi guys
I want transfer data from sheet invoice to sheet data .it should copy cells (B5,D5,I5) to columns A,B,C,D with considering repeat them to finsh last row and from row9 sholud copy from column A:I to sheet data from column D :L
invoice.xlsm
ABCDEFGHI
5invoice no4902client :as1date:16/11/2021
6Requested From
74902_
8itemcodebrandunitunit priceqtytotalpackagepackage count
91cca1tt1mm101201,200.00 box 2.00
102cca2tt2mm1010100.00 box 3.00
113cca3tt3mm1110110.00 box 4.00
124cca4tt4mm1211132.00 box 5.00
13    
14    
15    
16    
17    
18    
19    
20    
21    
22    
23    
24    
251,542.00total
26recordtax-discount
27168 - 267 - 21514/191/2259/5215.88tax
28n/m1,757.88net
29mm
invoice
Cell Formulas
RangeFormula
I5I5=TODAY()
B7B7=B5&"_"&B6
C13:C24C13=IFERROR(VLOOKUP(K13,الأسعار!$F:$G,2,0),"")
D13:D24D13=IFERROR(VLOOKUP(C13,الأسعار!$A:$C,3,0),"")
E13:E24E13=IFERROR(VLOOKUP(K13,الأسعار!$F:$H,3,0),"")
A13:A24A13=IF(C13="","",A12+1)
G9:G24G9=IFERROR(E9*F9,"")
G25G25=SUM(G9:G24)
G26G26=G25*-H26
G27G27=(G25-G26)*14%
G28G28=G25-G26+G27
Named Ranges
NameRefers ToCells
الأسعار!_FilterDatabase=الأسعار!$B$1:$B$253D13:D24


EXPECTED RESULT
invoice.xlsm
ABCDEFGHIJKLMN
1invoice noclient :date:ITEMcodebrandunitunit priceqtytotalpackagepackage count
24902as116/11/20211cca1tt1mm101201,200.00 box 2.00
34902as116/11/20212cca2tt2mm1010100.00 box 3.00
44902as116/11/20213cca3tt3mm1110110.00 box 4.00
54902as116/11/20214cca4tt4mm1211132.00 box 5.00
6
7
8
9
10
11
12
13
Data
Cell Formulas
RangeFormula
C2:C5C2=TODAY()
J2:J5J2=IFERROR(H2*I2,"")

I try without using loop , but it doesn't succeed . any help ,please?
VBA Code:
Sub copyinvoices()
Dim sh1, sh2 As Worksheet, lr1, lr2, r As Long
Set sh1 = Sheet1
Set sh2 = Sheet5
lr1 = sh1.Range("a" & Rows.Count).End(xlUp).Row
lr2 = sh2.Range("a" & Rows.Count).End(xlUp).Row + 1

sh2.Range("a2:a" & lr2).Value = sh1.Range("b5").Value
sh2.Range("b2:b" & lr2).Value = sh1.Range("i5").Value
sh2.Range("c2:c" & lr2).Value = sh1.Range("d5").Value
sh2.Range("d2:d" & lr2).Value = sh1.Range("d2:d" & lr1).Value
sh2.Range("e2:e" & lr2).Value = sh1.Range("e2:e" & lr1).Value
sh2.Range("f2:f" & lr2).Value = sh1.Range("f2:f" & lr1).Value
sh2.Range("g2:g" & lr2).Value = sh1.Range("g2:g" & lr1).Value
sh2.Range("h2:h" & lr2).Value = sh1.Range("h2:h" & lr1).Value
sh2.Range("i2:i" & lr2).Value = sh1.Range("i2:i" & lr1).Value
sh2.Range("j2:j" & lr2).Value = sh1.Range("j2:j" & lr1).Value

End Sub
 
Last edited:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi
Try
VBA Code:
Sub test()
Dim a, a1
With Sheets("invoice")
a1 = Array(.Range("B5"), .Range("D5"), .Range("I5"))
a = .Range(.Range("B9"), .Range("B9").End(xlDown)).Offset(, -1).Resize(, 9)
With Sheets("Data")
.Range("A2").Resize(UBound(a), 3) = a1
.Range("D2").Resize(UBound(a), UBound(a, 2)) = a
.Range("D2").Resize(UBound(a), UBound(a, 2)).Select
.Columns(3).NumberFormat = "dd/mm/yyyy"
End With: End With
End Sub
 
Upvote 0
that's great ! just curiosity instead of repeat (B5,D5,I5) can I merge like this
invoice.xlsm
ABCDEFGHIJKLM
1invoice noclient :date:ITEMcodebrandunitunit priceqtytotalpackagepackage count
24902as116/11/20211cca1tt1mm101201,200.00 box 2.00
32cca2tt2mm1010100.00 box 3.00
43cca3tt3mm1110110.00 box 4.00
54cca4tt4mm1211132.00 box 5.00
Data
 
Upvote 0
VBA Code:
Sub test()
Dim a, a1
With Sheets("invoice")
a1 = Array(.Range("B5"), .Range("D5"), .Range("I5"))
a = .Range(.Range("B9"), .Range("B9").End(xlDown)).Offset(, -1).Resize(, 9)
With Sheets("Data")
.Range("A2").Resize(UBound(a)).MergeCells = True
.Range("B2").Resize(UBound(a)).MergeCells = True
.Range("C2").Resize(UBound(a)).MergeCells = True
With Union(.Range("A2").Resize(UBound(a)), .Range("B2").Resize(UBound(a)), .Range("C2").Resize(UBound(a)))
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlCenter
 End With
.Range("A2").Resize(, 3) = a1
.Range("D2").Resize(UBound(a), UBound(a, 2)) = a
.Range("D2").Resize(UBound(a), UBound(a, 2)).Select
.Columns(3).NumberFormat = "dd/mm/yyyy"
End With: End With
End Sub
 
Upvote 0
thanks , but it gives " Method Of Range Class Failed error" in this line
VBA Code:
.Range("D2").Resize(UBound(a), UBound(a, 2)).Select
also I've found the code replace the data every time run the macro . it should copy to the bottom when run the macro repeatedly .

 
Upvote 0
Well
VBA Code:
Sub test()
Dim a, a1
With Sheets("invoice")
a1 = Array(.Range("B5"), .Range("D5"), .Range("I5"))
a = .Range(.Range("B9"), .Range("B9").End(xlDown)).Offset(, -1).Resize(, 9)
With Sheets("Data")
lr = .Cells(Rows.Count, 4).End(xlUp).Row + 1
.Range("A" & lr).Resize(UBound(a)).MergeCells = True
.Range("B" & lr).Resize(UBound(a)).MergeCells = True
.Range("C" & lr).Resize(UBound(a)).MergeCells = True
With Union(.Range("A" & lr).Resize(UBound(a)), .Range("B" & lr).Resize(UBound(a)), .Range("C" & lr).Resize(UBound(a)))
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlCenter
 End With
.Range("A" & lr).Resize(, 3) = a1
.Range("D" & lr).Resize(UBound(a), UBound(a, 2)) = a
.Columns(3).NumberFormat = "dd/mm/yyyy"
End With: End With
End Sub
 
Upvote 0
Solution
You are very welcome Mussa
Thank you for the feedback
Be happy & safe
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top