Copying a range of column from one sheet to other based on Date in Cloumn A

[amandeep99]

Hi,

I am newbie and trying to copy a value of columns from "B" to "BN" from source sheet to destination sheet column starting from "BU", if the value of cell in column "A" matches the yesterday's date.(which is not the last row of sheet)

For this , I have written a below script and facing Runtime error: 1004 copy area size is not matching the paste area

Please help me out of this.

Public Sub lrowcop()

Dim lastrow As Long
Dim d As Date

Dim wsCopy As Worksheet
Dim wsDest As Worksheet, r As Range

daycount3 = Format(Date - 1, "mmm")
sFolderName = Format(Now - 1, "yyyyMMdd")

Set wsCopy = Workbooks(sFolderName & " - 2 report " & daycount3 & "-2023").Worksheets(daycount3 & "-23")
Set wsDest = Workbooks("Daily bbook").Worksheets("Sheet1")
   

lastrow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row

d = [Today()-1]
wsCopy.Activate
For Each c In wsCopy.Range("A1:A" & lastrow)
If d = c.Value Then

      wsDest.Range("A" & lDestLastRow).Value = d
      wsCopy.Activate
      wsCopy.Range("B:BN").Copy
      wsDest.Range("BU" & lDestLastRow).PasteSpecial xlPasteValues
      Application.CutCopyMode = False

End If

Next
End Sub

 

[amandeep99]

hi need help to solve this