Share via

VBA Paste special error 91

Milan De Keersmaeker 1 Reputation point
2022-01-19T10:52:20.453+00:00

I have a macro that copies certain columns from one sheet to another sheet. Copying entire rows is not necessary as I don't need all the data. However, copying also pastes the formulas while I only need the values. I've tried everything with this but I keep getting the error message 91:

Sub CopyOverBudgetRecords()

Blad41.ListObjects("Kasboek").DataBodyRange.ClearContents

Call Inkomsten

End Sub

Sub Inkomsten()

Dim StatusCol As Range
Dim Status As Range
Dim PasteCell As Range
Dim PasteCell2 As Range

Set StatusCol = Blad36.Range("O9:O3000")

For Each Status In StatusCol

If Blad41.Range("C9") = "" Then
    Set PasteCell = Blad41.Range("C9")

    Set PasteCell = Blad41.Range("C8").End(xlDown).Offset(1, 0)
End If

If Status = "C" Then Status.Offset(0, -9).Resize(1, 1).Copy PasteCell.PasteSpecial(xlPasteValues)


   If Blad41.Range("D9") = "" Then
    Set PasteCell = Blad41.Range("D9")
Else

    Set PasteCell = Blad41.Range("D8").End(xlDown).Offset(1, 0)
End If

If Status = "C" Then Status.Offset(0, -4).Resize(1, 1).Copy PasteCell.PasteSpecial(xlPasteValues)

Next Status

Call Uitgaven

End Sub

Sub Uitgaven()

Dim StatusCol As Range
Dim Status As Range
Dim PasteCell As Range
Dim PasteCell2 As Range

Set StatusCol = Blad37.Range("S9:S3000")

For Each Status In StatusCol

If Blad41.Range("C9") = "" Then
    Set PasteCell = Blad41.Range("C9")
Else

    Set PasteCell = Blad41.Range("C8").End(xlDown).Offset(1, 0)
End If

If Status = "C" Then Status.Offset(0, -14).Resize(1, 1).Copy PasteCell

   If Blad41.Range("E9") = "" Then
    Set PasteCell = Blad41.Range("E9")
Else

    Set PasteCell = Blad41.Range("E8").End(xlDown).Offset(1, 0)
End If

If Status = "C" Then Status.Offset(0, -3).Resize(1, 1).Copy PasteCell

Next Status

    MsgBox "Kasboek is bijgewerkt", vbOKOnly

End Sub

Developer technologies | Visual Basic for Applications

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.