Copy data from cell range from previous day

[Pedro Mendes]

Hi all,

So I am with a small challenge today.
I have been working in some file I am not figuring out hot to solve this.

So my problem:

I have a Monthly Excel file, with some sheets. This sheets names (all days in the month) are the dates, as example: 01.01.2021, 02.01,2021.... etc...
What I am looking for is is a way, that I click a button, pops up a message asking if I want to copy some data from the previous day. If answer is yes it copies, if no, do nothing.

Manually saying that I want to do this for that specific day I can do. But I was trying to figure out a way to do this independent of the day or the month (Because I will have one workbook for each month), and I didn't want to be writing code for each day, every month....


I don't know if I make myself clear enough, but I am attaching some images of an example so you can see what I have and probably it will be easy to understand what i need.

Thank you all and have a nice day.

Regards,
Pedro Mendes

 

[JackDanIce]

Try:

Sub Copy_Prev_Day()
    
    Dim w As Worksheet
    Dim n(1) As Variant
    Dim l As Variant
    
    If MsgBox("Copy previous day's data?", vbYesNoCancel, "Copy Data") = vbYes Then
        On Error Resume Next
        n(0) = Format(Application.WorkDay(Now, -1), "dd.mm.yyyy")
        n(1) = Format(Now, "dd.mm.yyyy")
        Set w = Sheets(n(0))
        If Not w Is Nothing Then
            l = Lasts(w)
            Sheets(n(1)).Cells(1, 1).Resize(l(0), l(1)).Value = w.Cells(1, 1).Resize(l(0), l(1)).Value
        Else
            MsgBox "Sheet: " & n(0) & " not found!", vbExclamation, "Sheet Not Found"
        End If
        Set w = Nothing: Erase n
        On Error Resume Next
    End If

End Sub

Private Function Lasts(w As Worksheet) As Variant
    
    Dim x As Long: Dim y As Long
    
    With w
        x = .Cells.Find("*", .Cells(1), xlFormulas, xlPart, xlByRows, xlPrevious).Row
        y = .Cells.Find("*", .Cells(1), xlFormulas, xlPart, xlByColumns, xlPrevious).Column
    End With
    
    Lasts = Array(x, y)
End Function

 

[Pedro Mendes]

Hi JackDanlce,
Thank you for the help.
Wish you a Wonderful weekend.

 

[JackDanIce]

You're welcome @Pedro Mendes, glad it helped and thank you for the feedback Enjoy yours also