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

fadee2

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

VBA Code:
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....
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

bootdat

New Member
Joined
Nov 22, 2018
Messages
31
try

VBA Code:
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:
VBA Code:
Sheets("invoice").Range(Cells(17, 3), Cells(lastrow_src, 7)).Copy

application-defined or object-defined error
 

fadee2

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


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


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

Watch MrExcel Video

Forum statistics

Threads
1,123,249
Messages
5,600,537
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