Macro will copy formula instead of value

  • Thread starter Thread starter Cimjet
  • Start date Start date
C

Cimjet

Hi Everyone
I've got this macro for a summary sheet but it's pasting the formulas and
formating that I don't want.
I tried different things but no success.
----------------------------------------------
Sub SummaryPlus()
'Increment invoice number
Sheets("Facture").Range("K2").Value =
Sheets("Facture").Range("K2").Value + 1
ThisWorkbook.Save >>(This works OK)<<
'Date
Sheets("Facture").Range("B4") = Date >>(This works OK)<<


'Find the last row and copy/paste information from Facture worksheet

'Invoice Number
lastrow = Sheets("Summary").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Sheets("Facture").Range("K2").Copy _
Destination:=Sheets("Summary").Range("A" & lastrow + 1)
'Customer Name
lastrow = Sheets("Summary").Cells(Cells.Rows.Count, "B").End(xlUp).Row
Sheets("Facture").Range("G1").Copy _
Destination:=Sheets("Summary").Range("B" & lastrow + 1)
'Address
lastrow = Sheets("Summary").Cells(Cells.Rows.Count, "C").End(xlUp).Row
Sheets("Facture").Range("G2").Copy _
Destination:=Sheets("Summary").Range("C" & lastrow + 1)
'Labor Cost
lastrow = Sheets("Summary").Cells(Cells.Rows.Count, "d").End(xlUp).Row
Sheets("Facture").Range("L37").Copy
Destination:=Sheets("Summary").Range("d" & lastrow + 1) >>(This does not works
OK)<<
End Sub
------------------------------------------------------------------
I tried this :>>lastrow = Sheets("Summary").Cells(Cells.Rows.Count,
"A").End(xlUp).Row
Sheets("Facture").Range("K2").Copy _
Destination:=Sheets("Summary").Range("A" & lastrow +
1).pastespecial _
paste:=xlpastevalues >>(It gives an error )

I tried this also :>>lastrow = Sheets("Summary").Cells(Cells.Rows.Count,
"A").End(xlUp).Row
Sheets("Facture").Range("K2").Copy _
Destination:=Sheets("Summary").Range("A" & lastrow +
1).value _
= workSheets("Facture").range("K2").value
 
Try:
Sheets("Summary").Cells(lastrow + 1, "A").Value = _
Sheets("Facture").Range("K2").Value
 
Hi Garry
Thanks for taking the time to help me.
It's not working. It copies the info on the top line removing my heading and
will keep pasting on that same row. It's not stepping down each time I execute
the macro.
Regards
Cimjet
 
If 'Source' value is Sheets("Facture").Range("K2").Value,
AND Sheets("Summary") is empty except for headings in Row1,
then this...

Sub CopyData()
Dim lLastRow As Long
With Sheets("Summary")
lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(lLastRow + 1, "A").Value = _
Sheets("Facture").Range("K2").Value
End With
End Sub

...puts the 'Source' value into Sheets("Summary").Range("A2")! Next
round it puts the value into 'A3', ..and so on.
 
Slightly different approach if you want to increment rows continually
once the first blank row has been established..

Sub CopyData2()
Dim lNextRow As Long
With Sheets("Summary")
lNextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Cells(lNextRow, "A").Value = Sheets("Facture").Range("K2").Value
lNextRow = lNextRow + 1
.Cells(lNextRow, "A").Value = Sheets("Facture").Range("K3").Value
lNextRow = lNextRow + 1
'..and so on
End With
End Sub
 
Back
Top