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]

Not sure what to make of your edit.
If you only have one sheet starting with W.E. maybe something like this:

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

 

[NoSparks]

I'm assuming this is a follow up to your previous question.

If the button is on the sheet you'll be coping from you could
use With ActiveSheet in place of With Sheets("ABACUS")

And if the button is not on the sheet you'll be coping from (and even if it is) you can use the sheet code name.
Looking under VBAProject (Your Workbook Name)
under Microsoft Excel Objects
the sheets have 2 names, the code name and the (tab name)
so it would be like With Sheet1 instead of With Sheets("ABACUS")

For your after thought, here's how I would do it.
Others will shorten up the .Find instruction but this way you know what's what.

Sub Button3_Click()

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

With Sheet3     '<---- code name change to what your's actually is
    WKend = .Range("M2").Value2
    arr = .Range("AI21:AI33").Value
End With

With Sheets("OVERTIME").Range("A:A")
    Set fndRng = .Find(What:=WKend, _
                       LookIn:=xlValues, _
                       LookAt:=xlWhole, _
                       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]

Sorry guys, don't think my original was very clear.
I have a worksheet named "ABACUS" which when running a code saves it but renames the worksheet as W.E. combined with a date in cell C2, which is formatted as dd/mm/yy.
I would like to get a name of the this new sheet to look like "W.E.dd.mm.yy"
The section of code i have so far is,

ActiveSheet.Copy After:=Worksheets("OVERTIME")

On Error Resume Next

ActiveSheet.Name = "W.E." & Range("C2").Value

Many thanks, again..

 

[bdt]

The result I get at the moment is "ABACUS (2)" its just keeping the name of the original worksheet.
Hopefully a reasonably straight forward fix for someone who understands vba

 

[Alex Blakenburg]

On which sheet is the cell C2 with the date on it ?
If it was on ABACUS and therefore on the newly created sheet then it should be just this:

ActiveSheet.Copy After:=Worksheets("OVERTIME")
ActiveSheet.Name = "W.E." & Format(Range("C2").Value, "dd.mm.yyyy")

 

[bdt]

Hi,
C2 is on sheet "ABACUS"

 

[Alex Blakenburg]

Then the code I gave you should work.
You could specify the sheet name as being Abacus but since the active sheet is a copy of Abacus it should have the same value in C2

 

[bdt]

Hi, yes your code did work, just took me a bit of time getting things exactly how I need them
Many thanks

 

[NoSparks]

On Error Resume Next
is suppressing the error message that would tell you what the problem is.
Remove it and see.

Best not to be used except for expected errors with code to handle them.