CODEWITHSUNDEEP
excel-formula
Nathaniel Arnido Congreso
Nathaniel Arnido Congreso

Reputation: 47

copy and paste as values to another sheet, excel macro

I'm new with Macro and I want to create a simple copy and paste excel formula from one sheet to another. But the thing is the main data has a formula inside the cell and it wont let me copy and paste as values it to another cell.

Sub selectpasting()
Dim Lastrow As Long, erow As Long


Lastrow = Sheets("attendance").Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To Lastrow


   If Sheets("attendance").Cells(i, 3) = "absent" Then

    Sheets("attendance").Cells(i, 1).copy
    erow = Sheets("forpasting").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    Sheets("attendance").Paste Destination:=Sheets("forpasting").Cells(erow, 1)


    Sheets("attendance").Cells(i, 3).copy
    Sheets("attendance").Paste Destination:=Sheets("forpasting").Cells(erow, 2)

End If

Next i


Application.CutCopyMode = False
Sheets("forpasting").Columns.AutoFit
Range("A1").Select

End Sub

Upvotes: 1

Views: 186

Answers (1)

Wizhi
Wizhi

Reputation: 6549

Change this row:

Sheets("attendance").Paste Destination:=Sheets("forpasting").Cells(erow, 1)

To:

Sheets("forpasting").Cells(erow, 1).PasteSpecial xlValues

The complete code would be:

Sub selectpasting()
Dim Lastrow As Long, erow As Long
Dim i As Long

Lastrow = Sheets("attendance").Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To Lastrow


   If Sheets("attendance").Cells(i, 3) = "absent" Then

    Sheets("attendance").Cells(i, 1).Copy
    erow = Sheets("forpasting").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    Sheets("forpasting").Cells(erow, 1).PasteSpecial xlValues


    Sheets("attendance").Cells(i, 3).Copy
    Sheets("forpasting").Cells(erow, 2).PasteSpecial xlValues
End If

Next i


Application.CutCopyMode = False
Sheets("forpasting").Columns.AutoFit
Range("A1").Select

End Sub

The code above is quite slow (try both the codes and you would notice that the below is way faster).. The reason is that in the above excel needs to determine/evaluate if the the cell properties needs to be pasted or not due to ".copy". It's one approach when you need to copy/paste cell formats etc.

In your case you only interested in the value the cells shows. So you could just pick the value and copy it.

I would therefore recommend you to change it to:

Sub selectpasting_2()
Dim Lastrow As Long, erow As Long
Dim i As Long

Lastrow = Sheets("attendance").Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To Lastrow


   If Sheets("attendance").Cells(i, 3) = "absent" Then


    erow = Sheets("forpasting").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    Sheets("forpasting").Cells(erow, 1) = Sheets("attendance").Cells(i, 1)

    Sheets("forpasting").Cells(erow, 2) = Sheets("attendance").Cells(i, 3)
End If

Next i


Application.CutCopyMode = False
Sheets("forpasting").Columns.AutoFit
Range("A1").Select

End Sub

Upvotes: 1

Related Questions