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
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