RTE 13 Type mismatch using Object Copy

[dacmelaus]

Hi, I am searching a Table column, TblDayYear[Date], that has dates. when the date matches a worksheet cell Named DataDate, I want to copy a Table column, TblDayVis[Parcels], with 11 cells to a Table row, TblDayYear[[PAdult18-25]:[PTotVisits]] with 11 cells. Each Table is in a different worksheet I have used PasteSpectial with values and Transpose=True However, I get RTE 13 Type Mismatch on the Highlighted statement after I get the expected match on the date 5/7/23. Appreciate ideas on why. I tried using the Add-in XL2BB but it kept locking Excel so it was not responding. Thanks for spending the time to review

Sub CopyParcel2VisStats()
'
' CopyParcel2VisStats Macro
'
Dim iDateRange As Range
Dim iCell As Range
Dim iSysDate As Date
Dim iDataDate As Date
Set iDateRange = Range("TblDayYear[Date]")
iDataDate = Range("DataDate").Value
iSysDate = Range("SysDate").Value

For Each iCell In Range("TblDayYear[Date]")
If iCell.Value = iDataDate Then
Range("TblDayYear[[PAdult18-25]:[PTotVisits]]").PasteSpecial xlPasteValues, Transpose:=True = _
Range("TblDayVis[Parcels]").Value
End If
MsgBox (iCell)
' MsgBox (iDataDate)

Next iCell
End Sub

 

[Alex Blakenburg]

There is nothing in your copy line that limits the copy range to the selected row. Try:
(untested)

If iCell.Value = iDataDate Then
    Intersect(iCell.EntireRow, Range("TblDayYear[[PAdult18-25]:[PTotVisits]]")).PasteSpecial xlPasteValues, _
        Transpose:=True = Range("TblDayVis[Parcels]").Value
End If

 

[dacmelaus]

There is nothing in your copy line that limits the copy range to the selected row. Try:
(untested)

If iCell.Value = iDataDate Then
    Intersect(iCell.EntireRow, Range("TblDayYear[[PAdult18-25]:[PTotVisits]]")).PasteSpecial xlPasteValues, _
        Transpose:=True = Range("TblDayVis[Parcels]").Value
End If

Alex, thank you for reviewing and responding. I am sure the suggestion will assist in limiting the range. But I still get the RTE 13 and so cannot fully test the suggestion. Thanks

 

[Alex Blakenburg]

Ok there were a few changes.
• you need a separate copy line and a paste special line.
• you need to identify the row you want to copy (as per previous post)
• drop the .Values as the end. The range is where you want to paste it, the xlPasteValues is how you want to paste it.

See how you go with this.

        If iCell.Value = iDataDate Then
            Intersect(iCell.EntireRow, Range("TblDayYear[[PAdult18-25]:[PTotVisits]]")).Copy    ' Copy
            Range("TblDayVis[Parcels]").PasteSpecial Paste:=xlPasteValues, Transpose:=True      ' PasteSpecial
        End If

 

[dacmelaus]

Ok there were a few changes.
• you need a separate copy line and a paste special line.
• you need to identify the row you want to copy (as per previous post)
• drop the .Values as the end. The range is where you want to paste it, the xlPasteValues is how you want to paste it.

See how you go with this.

        If iCell.Value = iDataDate Then
            Intersect(iCell.EntireRow, Range("TblDayYear[[PAdult18-25]:[PTotVisits]]")).Copy    ' Copy
            Range("TblDayVis[Parcels]").PasteSpecial Paste:=xlPasteValues, Transpose:=True      ' PasteSpecial
        End If

Alex, Thanks, The Range TblDayYear[Parcels] is the source for the copy. So, I reversed the ranges. This gave the result I wanted but did not stop the search. As it is a list of dates for the year there will not be another match, so I added an 'Exit For' at the bottom of the IF statement. This worked perfectly. Thank you for your assistance, very much appreciated.

For Each iCell In Range("TblDayYear[Date]")
If iCell.Value = iDataDate Then
Range("TblDayVis[Parcels]").Copy
Intersect(iCell.EntireRow, Range("TblDayYear[[PAdult18-25]:[PTotVisits]]")).PasteSpecial Paste:=xlPasteValues, Transpose:=True ' PasteSpecial
Exit For
End If

 

[Alex Blakenburg]

You're welcome. Glad I could help and that you were able to modify it to suit.