Macro will copy formula instead of value


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
 
Ad

Advertisements

G

GS

Try:
Sheets("Summary").Cells(lastrow + 1, "A").Value = _
Sheets("Facture").Range("K2").Value
 
C

Cimjet

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
 
G

GS

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

GS

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top