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

 

[fadee2]

try

Dim lastrow_dest As Long
Dim lastrow_src As Long
Dim s As Integer

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

s = Sheets("invoice").Range(Cells(17, 3), Cells(lastrow_src, 7)).Rows.Count              'count total number of rows in items, this will serve as our endpoint for inv no, date and customer name entries

Sheets("invoice data").Range("d" & lastrow_dest + 1).PasteSpecial Paste:=xlPasteValues    'we paste your items to invoice data first

For i = lastrow_dest To lastrow_dest + s - 1                       ' then we bring in the inv no, date and customer name to the invoice data
    With Sheets("invoice data")
        .Range("a" & i + 1).Value = Sheets("invoice").Range("j2").Value
        .Range("b" & i + 1).Value = Sheets("invoice").Range("k2").Value
        .Range("c" & i + 1).Value = Sheets("invoice").Range("l2").Value
    End With
Next i

HTH....

 

[bootdat]

try

Dim lastrow_dest As Long
Dim lastrow_src As Long
Dim s As Integer

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

s = Sheets("invoice").Range(Cells(17, 3), Cells(lastrow_src, 7)).Rows.Count              'count total number of rows in items, this will serve as our endpoint for inv no, date and customer name entries

Sheets("invoice data").Range("d" & lastrow_dest + 1).PasteSpecial Paste:=xlPasteValues    'we paste your items to invoice data first

For i = lastrow_dest To lastrow_dest + s - 1                       ' then we bring in the inv no, date and customer name to the invoice data
    With Sheets("invoice data")
        .Range("a" & i + 1).Value = Sheets("invoice").Range("j2").Value
        .Range("b" & i + 1).Value = Sheets("invoice").Range("k2").Value
        .Range("c" & i + 1).Value = Sheets("invoice").Range("l2").Value
    End With
Next i

HTH....

Hello, thanks

so i did try it and i had an error here:

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

application-defined or object-defined error

 

[fadee2]

try

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

......

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