refering to a worksheet with a changing name

[bdt]

Hi all, i have a worksheet, which has a changing name, W.E.**. I'd like to copy a range of cells from this worksheet to one named OVERTIME, using a button. I'm at a loss how to refer to the worksheet I'm copying from.
Any help much appreciated. The code below is what I have so far, where ("ABACUS") is the active worksheet i'm copying from.

Sub Button3_Click()

    'copy overtime sunday
With Sheets("ABACUS")
    WKend = .Range("M2").Value2
    arr = .Range("AI21:AI33").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

EDIT:
HI, a bit of an after thought.
On the sheet ("OVERTIME") I'm looking to paste / write over the values into columns B to M, where column A has a value equal to "M2" in the original active worksheet.
Hope this makes some kind of sense.
Thanks

 

[Alex Blakenburg]

You're welcome. Glad we got there in the end.

Depending on what else the code is doing you might want to set a reference to it while its active eg

Dim shtCurrentWk as worksheet
Set shtCurrentWk = ActiveSheet

 

[bdt]

thanks for your suggestion, have now removed it.

 

[bdt]

Hi, in need of help again.
The original question was resolved, but on developing the spreadsheet further, I@m now after the following.
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

 

[Alex Blakenburg]

I am about to log off its very late here.
From a quick look you can try and change this line to the below.

If InStr(1, sht.Name, "W.E.", vbTextCompare) > 0 Then Set shtSrc = sht

 

[bdt]

unfortunately this isn't working for me, but thanks

 

[Joe4]

When you get your error message, do you have a "Debug" button option?
If so, and you click that button, which line of code does it highlight?

 

[NoSparks]

The way I interpret things you're not asking the same question but things are so intermingled it's hard to tell.

Try this

Sub Button3_Click()

    Dim WKend As Date
    Dim arr As Variant
    Dim fndRng As Range

With ActiveSheet
    WKend = .Range("M2").Value
    arr = .Range("AI21:AI33").Value
End With

With Sheets("OVERTIME").Range("A:A")
    Set fndRng = .Find(What:=Format(WKend, "dd.mm.yy"), _
                       LookIn:=xlValues, _
                       LookAt:=xlPart, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlNext, _
                       MatchCase:=False)
                       
    If Not fndRng Is Nothing Then
        fndRng.Offset(, 1).Resize(, UBound(arr)).Value = Application.Transpose(arr)
    Else
        MsgBox "Sorry, did not find " & WKend
    End If
End With

End Sub

 

[bdt]

NoSparks once again you are a hero! many thanks does exactly what I was hoping for