Hi all,
I have to following VBA which works as its suppose to, its copying the data from one sheet to the other. how can I adjust it so it only copies and pastes the original cell value rather than the formula's, colours and formats?
any help would be great..
Private Sub CommandButton1_Click()
Dim lastrow As Long, Erow As Long
lastrow = Worksheets("Area").Cells(Rows.Count, 2).End(xlUp).Row
For i = 8 To lastrow
If Worksheets("Area").Cells(i, 22).Value = "Sale" Then
Worksheets("Area").Cells(i, 3).Copy
Erow = Worksheets("Cost").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Area").Paste Destination:=Worksheets("Cost").Cells(Erow + 1, 1)
Worksheets("Area").Cells(i, 7).Copy
Worksheets("Area").Paste Destination:=Worksheets("Cost").Cells(Erow + 1, 2)
Worksheets("Area").Cells(i, 9).Copy
Worksheets("Area").Paste Destination:=Worksheets("Cost").Cells(Erow + 1, 3)
End If
Next i
End Sub
I have to following VBA which works as its suppose to, its copying the data from one sheet to the other. how can I adjust it so it only copies and pastes the original cell value rather than the formula's, colours and formats?
any help would be great..
Private Sub CommandButton1_Click()
Dim lastrow As Long, Erow As Long
lastrow = Worksheets("Area").Cells(Rows.Count, 2).End(xlUp).Row
For i = 8 To lastrow
If Worksheets("Area").Cells(i, 22).Value = "Sale" Then
Worksheets("Area").Cells(i, 3).Copy
Erow = Worksheets("Cost").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Area").Paste Destination:=Worksheets("Cost").Cells(Erow + 1, 1)
Worksheets("Area").Cells(i, 7).Copy
Worksheets("Area").Paste Destination:=Worksheets("Cost").Cells(Erow + 1, 2)
Worksheets("Area").Cells(i, 9).Copy
Worksheets("Area").Paste Destination:=Worksheets("Cost").Cells(Erow + 1, 3)
End If
Next i
End Sub