copy range of cells

[bdt]

Hi, in need of help again.

I wish to copy a range of cells AI21:AI32 from a worksheet to a worksheet named "OVERTIME", where the date in M2 = the corresponding date in column A of "OVERTIME". The source worksheet is named "W.E.dd.mm.yy" where the date changes depending on which source worksheet is being used, but each source sheet is set out exactly the same. When clicking button 3 I get run time error !91, object variable or with block variable not set. The code I have is;

Sub Button3_Click()
'copy overtime sunday
Dim shtSrc As Worksheet
Dim sht As Worksheet
Dim WKend As Date
Dim writerow As Long
Dim arr As Variant

For Each sht In ActiveWorkbook.Worksheets
If InStr(1, sht.Name, "W.E.**.**.**", vbTextCompare) > 0 Then Set shtSrc = sht
Next sht

With shtSrc
WKend = .Range("M2").Value2
arr = .Range("AI21:AI32").Value
End With

With Sheets("OVERTIME")
' last used row in column B plus 1
writerow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
.Range("A" & writerow) = WKend
.Range("B" & writerow).Resize(, UBound(arr)).Value = Application.Transpose(arr)
End With

End Sub


Again, any guidance much appreciated

 

[Joe4]

That looks like the code someone posted over here on your original thread: refering to a worksheet with a changing name

If you have questions about their code, you should continue in the original thread and not start a new one, or else there is a good chance they will not see it.