Copy data from cell range from previous day

Pedro Mendes

New Member
Joined
Jun 8, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • im1.JPG
    im1.JPG
    113.4 KB · Views: 12
  • im2.JPG
    im2.JPG
    123.6 KB · Views: 12
  • im3.JPG
    im3.JPG
    78 KB · Views: 12

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Try:
VBA Code:
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
 
Last edited:
Upvote 0
Solution

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top