Copying Invoice Data To Another Sheet

[bootdat]

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

 

[dmt32]

Hi,
if your code is largely doing what you want, try using the Range.Text property & see if this solves your issue

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]

Hi,
if your code is largely doing what you want, try using the Range.Text property & see if this solves your issue

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

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]

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]

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
	B	C	D	E	F	G
16	Item #	Qty	Description	Unit Price	Discount	Line Total
17
18		2	2 WEEKS INTERMEDIATE CLASS	GHC 50.00	GHC 5.00	GHC 95.00
19		1	1 MONTH BEGINNERS CLASS	GHC 5.00		GHC 5.00
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39		2	3 DAYS WHIPPING CREAM CLASS	GHC 5.00	GHC 1.00	GHC 9.00
Invoice

Cell Formulas
Range	Formula
G17:G39	G17	=IF(SUM(C17)>0,SUM((C17*E17)-F17),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B17:G40	Expression	=MOD(ROW(),2)=1	text	NO

but the code returns this results if there is a formula in column G:

POSv2.xlsx
	A	B	C	D	E	F	G	H
1	Invoice Number	Date	Customer Name	Qty	Item Description	Unit Price	Discount	Total
2	OCT2000045	########	Trytest	2	2 WEEKS INTERMEDIATE CLASS	50	5	95
3	OCT2000045	########	Trytest	1	1 MONTH BEGINNERS CLASS	5		5
4	OCT2000045	########	Trytest	2	3 DAYS WHIPPING CREAM CLASS	5	1	9
5	OCT2000045	########	Trytest	2	2 WEEKS INTERMEDIATE CLASS	50	5	95
6	OCT2000045	########	Trytest	1	1 MONTH BEGINNERS CLASS	5		5
7	OCT2000045	########	Trytest	2	3 DAYS WHIPPING CREAM CLASS	5	1	9
8	OCT2000045	########	Trytest	2	2 WEEKS INTERMEDIATE CLASS	50	5	95
9	OCT2000045	########	Trytest	1	1 MONTH BEGINNERS CLASS	5		5
10	OCT2000045	########	Trytest	2	3 DAYS WHIPPING CREAM CLASS	5	1	9
11	OCT2000045	########	Trytest	2	2 WEEKS INTERMEDIATE CLASS	50	5	95
12	OCT2000045	########	Trytest	1	1 MONTH BEGINNERS CLASS	5		5
13	OCT2000045	########	Trytest	2	3 DAYS WHIPPING CREAM CLASS	5	1	9
14	OCT2000045	########	Trytest	2	2 WEEKS INTERMEDIATE CLASS	50	5	95
15	OCT2000045	########	Trytest	1	1 MONTH BEGINNERS CLASS	5		5
16	OCT2000045	########	Trytest	2	3 DAYS WHIPPING CREAM CLASS	5	1	9
17	OCT2000045	########	Trytest
18	OCT2000045	########	Trytest
19	OCT2000045	########	Trytest
20	OCT2000045	########	Trytest
21	OCT2000045	########	Trytest
22	OCT2000045	########	Trytest
23	OCT2000045	########	Trytest
24	OCT2000045	########	Trytest
25	OCT2000045	########	Trytest
26	OCT2000045	########	Trytest
27	OCT2000045	########	Trytest
28	OCT2000045	########	Trytest
29	OCT2000045	########	Trytest
30	OCT2000045	########	Trytest
31	OCT2000045	########	Trytest
32	OCT2000045	########	Trytest
33	OCT2000045	########	Trytest
34	OCT2000045	########	Trytest
35	OCT2000045	########	Trytest
36	OCT2000045	########	Trytest
37	OCT2000045	########	Trytest
38	OCT2000045	########	Trytest
39	OCT2000045	########	Trytest
Invoice Data

but without formulas in column G it runs fine :

POSv2.xlsx
	A	B	C	D	E	F	G	H
1	Invoice Number	Date	Customer Name	Qty	Item Description	Unit Price	Discount	Total
2	OCT2000045	########	Trytest	2	2 WEEKS INTERMEDIATE CLASS	50	5	95
3	OCT2000045	########	Trytest	1	1 MONTH BEGINNERS CLASS	5		5
4	OCT2000045	########	Trytest	2	3 DAYS WHIPPING CREAM CLASS	5	1	9
5	OCT2000045	########	Trytest	2	2 WEEKS INTERMEDIATE CLASS	50	5	95
6	OCT2000045	########	Trytest	1	1 MONTH BEGINNERS CLASS	5		5
7	OCT2000045	########	Trytest	2	3 DAYS WHIPPING CREAM CLASS	5	1	9
8	OCT2000045	########	Trytest	2	2 WEEKS INTERMEDIATE CLASS	50	5	95
9	OCT2000045	########	Trytest	1	1 MONTH BEGINNERS CLASS	5		5
10	OCT2000045	########	Trytest	2	3 DAYS WHIPPING CREAM CLASS	5	1	9
11	OCT2000045	########	Trytest	2	2 WEEKS INTERMEDIATE CLASS	50	5	95
12	OCT2000045	########	Trytest	1	1 MONTH BEGINNERS CLASS	5		5
13	OCT2000045	########	Trytest	2	3 DAYS WHIPPING CREAM CLASS	5	1	9
14	OCT2000045	########	Trytest	2	2 WEEKS INTERMEDIATE CLASS	50	5	95
15	OCT2000045	########	Trytest	1	1 MONTH BEGINNERS CLASS	5		5
16	OCT2000045	########	Trytest	2	3 DAYS WHIPPING CREAM CLASS	5	1	9
17	OCT2000045	########	Trytest	2	2 WEEKS INTERMEDIATE CLASS	50	5	95
18	OCT2000045	########	Trytest	1	1 MONTH BEGINNERS CLASS	5		5
19	OCT2000045	########	Trytest	2	3 DAYS WHIPPING CREAM CLASS	5	1	9
Invoice Data

 

[bootdat]

Any help from anyone please ?

 

[fadee2]

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.

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]

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.

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
	A	B	C	D	E	F	G
1	Invoice Number	Date	Customer Name	Qty	Item Description	Unit Price	Discount
2
3			2	2 WEEKS INTERMEDIATE CLASS	50	5	95
4			1	1 MONTH BEGINNERS CLASS	5		5
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24			2	3 DAYS WHIPPING CREAM CLASS	5	1	9
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
16	Line Total
17
18	GHC 95.00
19	GHC 5.00
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39	GHC 9.00
Invoice

Cell Formulas
Range	Formula
G17:G39	G17	=IF(SUM(C17)>0,SUM((C17*E17)-F17),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B17:G40	Expression	=MOD(ROW(),2)=1	text	NO

 

[bootdat]

Hi, well this is the results of your code:

POSv2.xlsm
	A	B	C	D	E	F	G
1	Invoice Number	Date	Customer Name	Qty	Item Description	Unit Price	Discount
2
3			2	2 WEEKS INTERMEDIATE CLASS	50	5	95
4			1	1 MONTH BEGINNERS CLASS	5		5
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24			2	3 DAYS WHIPPING CREAM CLASS	5	1	9
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
16	Line Total
17
18	GHC 95.00
19	GHC 5.00
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39	GHC 9.00
Invoice

Cell Formulas
Range	Formula
G17:G39	G17	=IF(SUM(C17)>0,SUM((C17*E17)-F17),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B17:G40	Expression	=MOD(ROW(),2)=1	text	NO

I expect this results :

POSv2.xlsm
	A	B	C	D	E	F	G	H
1	Invoice Number	Date	Customer Name	Qty	Item Description	Unit Price	Discount	Total
2	OCT2000045	########	Trytest	2	2 WEEKS INTERMEDIATE CLASS	50	5	95
3	OCT2000045	########	Trytest	1	1 MONTH BEGINNERS CLASS	5		5
4	OCT2000045	########	Trytest	2	3 DAYS WHIPPING CREAM CLASS	5	1	9
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]

well u have to edit it to suite your needs, it is just another approach.