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
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