VBA paste special... why isn't this working?

[nicolehalliday]

Hello,

I have been trying to figure out why my paste special isn't working for SO long, I'm close to giving up so hopefully someone else can spot the mistake! Is highlights yellow on the pastespecial line, but I don't understand why the paste location is invalid and have tried countless variations of below. Thanks for your help!

Sub deletepastdiv()
Dim x As Long
Dim y As Long
Dim ticker As Range
Dim divfreq As Long
Dim csheet As Worksheet
Set csheet = Worksheets("Sheet2")
Dim histsheet As Worksheet
Set histsheet = Worksheets("Historical Divs")
Dim count As Long
Dim exdate As Date
Dim twodaysago As Date
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
startrow = 4
endrow = histsheet.Range("D" & startrow).End(xlDown).Row
hendrow = csheet.Range("D" & startrow).End(xlDown).Row
 
For x = startrow To endrow
 
 
    twodaysago = csheet.Range("K2")
    For y = startrow To hendrow
        If csheet.Range("D" & x) < twodaysago Then
                If endrow > 65535 Then
                endrow = startrow - 1
 
                exdate = csheet.Range("D" & y)
                exdaterow = x
                csheet.Range("D" & exdaterow, "L" & exdaterow).Cut
                histsheets.Range("D:L" & endrow + 1).PasteSpecial
End If
        End If
    Next y
Next x
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Jonmo1]

This is not a valid range reference..
histsheets.Range("D:L" & endrow + 1).PasteSpecial

If endrow = 35 then that would translate to
histsheets.Range("D:L36").PasteSpecial

It's missing the begin Row#
histsheets.Range("D??:L36").PasteSpecial

 

[VoG]

Paste Special isn't available after Cut. Try

csheet.Range("D" & exdaterow, "L" & exdaterow).Copy
histsheets.Range("D:L" & endrow + 1).PasteSpecial Paste:=xlPasteValues
csheet.Range("D" & exdaterow, "L" & exdaterow).ClearContents

 

[Jonmo1]

Combining both answers into 1...

csheet.Range("D" & exdaterow).Resize(1,9).Copy
histsheets.Range("D" & endrow + 1).Resize(1, 9).PasteSpecial Paste:=xlPasteValues
csheet.Range("D" & exdaterow).Resize(1, 9).ClearContents

 

[shg]

Or ...

    With csheet.Cells(exdaterow, "D").Resize(, 9)
        histsheets.Cells(endrow + 1, "D").Resize(, .Columns.Count) = .Value
        .ClearContents
    End With

 

[nicolehalliday]

Thanks for the replies. Still having problems because as was mentioned, the paste range is invalid. I tried changing it to this, but still not working. Any suggestions? Thanks!!

                    csheet.Range("D" & exdaterow, "L" & exdaterow).Copy
                    histsheets.Range("D" & endrow + 1, "L" & endrow + 1).PasteSpecial paste:=xlPasteValues
                    csheet.Range("D" & exdaterow, "L" & exdaterow).ClearContents

 

[Jonmo1]

The code works for me, so long as all variables are correctly defined and given values.

Describe "not working"
Do you get an error? What error?
Does nothing happen?
Is the wrong data being copied?
Or pasted to the wrong place?

What are the values of the variables at the time of the error..
exdaterow and endrow ?

 

[nicolehalliday]

Wohooo, got it!!! Thanks everyone for saving me time and sanity. Have a nice day