Copying Invoice Data To Another Sheet

bootdat

New Member
Joined
Nov 22, 2018
Messages
31
Hi,
I plan on creating a sales system and i want my invoice details in the sheet "invoice" to be copied and saved in another sheet called "Invoice Data" before printing...
But i find to have this problem where if there is a formula in the last column of the table, the copy and pasting doesn't work at it should.. rather it does it as you can see in invoice data.png
and if i take out the formula , the code just works fine as seen in without formula.png

so I'm wondering if there could be another way it can copy and paste only the values and ignore the formula although it doesn't copy and past the formula..


Sub Print_Bill()
'

'
' Print_Bill Macro

Dim rng As Range
Dim temp As Variant
Dim i As Long
Dim a As Long
Dim rng_dest As Range


i = 1

Set rng_dest = Sheets("Invoice Data").Range("D:H")

' Find first empty row in columns D:H on sheet Invoice data

Do Until WorksheetFunction.CountA(rng_dest.Rows(i)) = 0

i = i + 1

Loop

'Copy range C17:G39 on sheet Invoice to Variant array

Set rng = Sheets("Invoice").Range("C17:G39")

' Copy rows containing values to sheet Invoice data

For a = 1 To rng.Rows.Count

If WorksheetFunction.CountA(rng.Rows(a)) <> 0 Then

rng_dest.Rows(i).Value = rng.Rows(a).Value

'Copy Invoice number

Sheets("Invoice Data").Range("A" & i).Value = Sheets("Invoice").Range("F6").Value

'Copy Date

Sheets("Invoice Data").Range("B" & i).Value = Sheets("Invoice").Range("F5").Value

'Copy Customer name

Sheets("Invoice Data").Range("C" & i).Value = Sheets("Invoice").Range("C9").Value

i = i + 1

End If

Next a


End Sub
 

Attachments

  • invoice data.PNG
    invoice data.PNG
    34.5 KB · Views: 14
  • invoice.PNG
    invoice.PNG
    25 KB · Views: 14
  • Without formula.PNG
    Without formula.PNG
    26.8 KB · Views: 14

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,387
Office Version
  1. 2019
Platform
  1. Windows
Hi,
if your code is largely doing what you want, try using the Range.Text property & see if this solves your issue

Rich (BB code):
rng_dest.Rows(i).Value = Rng.Rows(a).Text

'Copy Invoice number

Sheets("Invoice Data").Range("A" & i).Value = Sheets("Invoice").Range("F6").Text

'Copy Date

Sheets("Invoice Data").Range("B" & i).Value = Sheets("Invoice").Range("F5").Text

'Copy Customer name

Sheets("Invoice Data").Range("C" & i).Value = Sheets("Invoice").Range("C9").Text

Also, rather than posting images of your worksheets, use MrExcel Addin xl2BB :XL2BB - Excel Range to BBCode which captures the cells that can be copied & maybe, someone will offer alternative suggestions you can try.

Dave
 

bootdat

New Member
Joined
Nov 22, 2018
Messages
31
Hi,
if your code is largely doing what you want, try using the Range.Text property & see if this solves your issue

Rich (BB code):
rng_dest.Rows(i).Value = Rng.Rows(a).Text

'Copy Invoice number

Sheets("Invoice Data").Range("A" & i).Value = Sheets("Invoice").Range("F6").Text

'Copy Date

Sheets("Invoice Data").Range("B" & i).Value = Sheets("Invoice").Range("F5").Text

'Copy Customer name

Sheets("Invoice Data").Range("C" & i).Value = Sheets("Invoice").Range("C9").Text

Also, rather than posting images of your worksheets, use MrExcel Addin xl2BB :XL2BB - Excel Range to BBCode which captures the cells that can be copied & maybe, someone will offer alternative suggestions you can try.

Dave
Okay sure. But I think my problem lies here

VBA Code:
Set rng = Sheets("Invoice").Range("C17:G39")

If column G contain formula the code doesn't work. But if it got no formula the code does paste correctly..
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,387
Office Version
  1. 2019
Platform
  1. Windows
Did you try all the suggested changes from Value to Text in your code?

If still having issues then post your worksheets using suggested Addin would be helpful to forum.

Dave
 

bootdat

New Member
Joined
Nov 22, 2018
Messages
31

ADVERTISEMENT

Did you try all the suggested changes from Value to Text in your code?

If still having issues then post your worksheets using suggested Addin would be helpful to forum.

Dave
hi,
the suggested changes from Value to Text didn't work...


so this is the table I'm copying data from:
POSv2.xlsx
BCDEFG
16Item #QtyDescriptionUnit PriceDiscountLine Total
17 
1822 WEEKS INTERMEDIATE CLASSGHC 50.00GHC 5.00GHC 95.00
1911 MONTH BEGINNERS CLASSGHC 5.00GHC 5.00
20 
21 
22 
23 
24 
25 
26 
27 
28 
29 
30 
31 
32 
33 
34 
35 
36 
37 
38 
3923 DAYS WHIPPING CREAM CLASSGHC 5.00GHC 1.00GHC 9.00
Invoice
Cell Formulas
RangeFormula
G17:G39G17=IF(SUM(C17)>0,SUM((C17*E17)-F17),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B17:G40Expression=MOD(ROW(),2)=1textNO



but the code returns this results if there is a formula in column G:
POSv2.xlsx
ABCDEFGH
1Invoice NumberDateCustomer NameQtyItem DescriptionUnit PriceDiscountTotal
2OCT2000045########Trytest22 WEEKS INTERMEDIATE CLASS50595
3OCT2000045########Trytest11 MONTH BEGINNERS CLASS55
4OCT2000045########Trytest23 DAYS WHIPPING CREAM CLASS519
5OCT2000045########Trytest22 WEEKS INTERMEDIATE CLASS50595
6OCT2000045########Trytest11 MONTH BEGINNERS CLASS55
7OCT2000045########Trytest23 DAYS WHIPPING CREAM CLASS519
8OCT2000045########Trytest22 WEEKS INTERMEDIATE CLASS50595
9OCT2000045########Trytest11 MONTH BEGINNERS CLASS55
10OCT2000045########Trytest23 DAYS WHIPPING CREAM CLASS519
11OCT2000045########Trytest22 WEEKS INTERMEDIATE CLASS50595
12OCT2000045########Trytest11 MONTH BEGINNERS CLASS55
13OCT2000045########Trytest23 DAYS WHIPPING CREAM CLASS519
14OCT2000045########Trytest22 WEEKS INTERMEDIATE CLASS50595
15OCT2000045########Trytest11 MONTH BEGINNERS CLASS55
16OCT2000045########Trytest23 DAYS WHIPPING CREAM CLASS519
17OCT2000045########Trytest
18OCT2000045########Trytest
19OCT2000045########Trytest
20OCT2000045########Trytest
21OCT2000045########Trytest
22OCT2000045########Trytest
23OCT2000045########Trytest
24OCT2000045########Trytest
25OCT2000045########Trytest
26OCT2000045########Trytest
27OCT2000045########Trytest
28OCT2000045########Trytest
29OCT2000045########Trytest
30OCT2000045########Trytest
31OCT2000045########Trytest
32OCT2000045########Trytest
33OCT2000045########Trytest
34OCT2000045########Trytest
35OCT2000045########Trytest
36OCT2000045########Trytest
37OCT2000045########Trytest
38OCT2000045########Trytest
39OCT2000045########Trytest
Invoice Data



but without formulas in column G it runs fine :
POSv2.xlsx
ABCDEFGH
1Invoice NumberDateCustomer NameQtyItem DescriptionUnit PriceDiscountTotal
2OCT2000045########Trytest22 WEEKS INTERMEDIATE CLASS50595
3OCT2000045########Trytest11 MONTH BEGINNERS CLASS55
4OCT2000045########Trytest23 DAYS WHIPPING CREAM CLASS519
5OCT2000045########Trytest22 WEEKS INTERMEDIATE CLASS50595
6OCT2000045########Trytest11 MONTH BEGINNERS CLASS55
7OCT2000045########Trytest23 DAYS WHIPPING CREAM CLASS519
8OCT2000045########Trytest22 WEEKS INTERMEDIATE CLASS50595
9OCT2000045########Trytest11 MONTH BEGINNERS CLASS55
10OCT2000045########Trytest23 DAYS WHIPPING CREAM CLASS519
11OCT2000045########Trytest22 WEEKS INTERMEDIATE CLASS50595
12OCT2000045########Trytest11 MONTH BEGINNERS CLASS55
13OCT2000045########Trytest23 DAYS WHIPPING CREAM CLASS519
14OCT2000045########Trytest22 WEEKS INTERMEDIATE CLASS50595
15OCT2000045########Trytest11 MONTH BEGINNERS CLASS55
16OCT2000045########Trytest23 DAYS WHIPPING CREAM CLASS519
17OCT2000045########Trytest22 WEEKS INTERMEDIATE CLASS50595
18OCT2000045########Trytest11 MONTH BEGINNERS CLASS55
19OCT2000045########Trytest23 DAYS WHIPPING CREAM CLASS519
Invoice Data
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
319
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi,
Your code is fine and should work. Didnt see an error in the code.
If you only want to copy and paste the data from the Invoice to Invoice data then you can try another approach.

VBA Code:
Dim lastrow_dest As Long
Dim lastrow_src As Long

With Sheets("invoice data")
    lastrow_dest = .Range("c" & .Rows.Count).End(xlUp).Row
End With

With Sheets("invoice")
    lastrow_src = .Range("c" & .Rows.Count).End(xlUp).Row
End With

Sheets("invoice").Range(Cells(17, 3), Cells(lastrow_src, 7)).Copy

Sheets("invoice data").Range("c" & lastrow_dest + 1).PasteSpecial Paste:=xlPasteValues

This will copy data from Invoice and paste it into Invoice data after the lastrow from column C is found.

Fahad
 

bootdat

New Member
Joined
Nov 22, 2018
Messages
31
Hi,
Your code is fine and should work. Didnt see an error in the code.
If you only want to copy and paste the data from the Invoice to Invoice data then you can try another approach.

VBA Code:
Dim lastrow_dest As Long
Dim lastrow_src As Long

With Sheets("invoice data")
    lastrow_dest = .Range("c" & .Rows.Count).End(xlUp).Row
End With

With Sheets("invoice")
    lastrow_src = .Range("c" & .Rows.Count).End(xlUp).Row
End With

Sheets("invoice").Range(Cells(17, 3), Cells(lastrow_src, 7)).Copy

Sheets("invoice data").Range("c" & lastrow_dest + 1).PasteSpecial Paste:=xlPasteValues

This will copy data from Invoice and paste it into Invoice data after the lastrow from column C is found.

Fahad
Hi, well this is the results of your code:

POSv2.xlsm
ABCDEFG
1Invoice NumberDateCustomer NameQtyItem DescriptionUnit PriceDiscount
2
322 WEEKS INTERMEDIATE CLASS50595
411 MONTH BEGINNERS CLASS55
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2423 DAYS WHIPPING CREAM CLASS519
Invoice Data


and it isn't quite what i was looking for..

also, i understand my vba should work fine, and it does works fine.
the only issue is that, it only works fine only if i dont have a formula in column G of my invoice sheet table:

POSv2.xlsm
G
16Line Total
17 
18GHC 95.00
19GHC 5.00
20 
21 
22 
23 
24 
25 
26 
27 
28 
29 
30 
31 
32 
33 
34 
35 
36 
37 
38 
39GHC 9.00
Invoice
Cell Formulas
RangeFormula
G17:G39G17=IF(SUM(C17)>0,SUM((C17*E17)-F17),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B17:G40Expression=MOD(ROW(),2)=1textNO
 

bootdat

New Member
Joined
Nov 22, 2018
Messages
31
Hi, well this is the results of your code:

POSv2.xlsm
ABCDEFG
1Invoice NumberDateCustomer NameQtyItem DescriptionUnit PriceDiscount
2
322 WEEKS INTERMEDIATE CLASS50595
411 MONTH BEGINNERS CLASS55
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2423 DAYS WHIPPING CREAM CLASS519
Invoice Data


and it isn't quite what i was looking for..

also, i understand my vba should work fine, and it does works fine.
the only issue is that, it only works fine only if i dont have a formula in column G of my invoice sheet table:

POSv2.xlsm
G
16Line Total
17 
18GHC 95.00
19GHC 5.00
20 
21 
22 
23 
24 
25 
26 
27 
28 
29 
30 
31 
32 
33 
34 
35 
36 
37 
38 
39GHC 9.00
Invoice
Cell Formulas
RangeFormula
G17:G39G17=IF(SUM(C17)>0,SUM((C17*E17)-F17),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B17:G40Expression=MOD(ROW(),2)=1textNO
I expect this results :

POSv2.xlsm
ABCDEFGH
1Invoice NumberDateCustomer NameQtyItem DescriptionUnit PriceDiscountTotal
2OCT2000045########Trytest22 WEEKS INTERMEDIATE CLASS50595
3OCT2000045########Trytest11 MONTH BEGINNERS CLASS55
4OCT2000045########Trytest23 DAYS WHIPPING CREAM CLASS519
Invoice Data


but i only get this results if there is no formula in column G of my invoice sheet table.

So how can i adjust the vba to get the above results when i have a formula in column G ?
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
319
Office Version
  1. 2019
Platform
  1. Windows
well u have to edit it to suite your needs, it is just another approach.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,248
Messages
5,600,535
Members
414,386
Latest member
PARAMATHMA SENTHILNATHAN

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
Top