Copy Paste From One Sheet To Another That Uses a New Date Every Day

[RandyD123]

All,

So I have a sheet named "DO Report 10-14-22", and the date changes every day to the current day. Each day it is a completely new file that is saved on IShare. I need to copy the data in this Workbook on the "MR" tab to another workbook in called "Checkpoint Summary 101422" on the "Sheet1" tab. The ranges would always be the same.

Range for "DO Report 10-14-22" is N5:O41 and it will always be that range. This file is on IShare so I would just open it manually and go to the "MR" tab so that sheet is active. Then I need to copy that data without formulas to a Workbook named "Checkpoint Summary 101422". This paste would go into the "Sheet1" tab which would already be open and active. The range on this sheet is "D11:E47".

Both Workbooks change date every day and I always have to use the previous days workbooks. Meaning on 10/14 I need the workbooks from 10/13. I can open both workbooks manually. It would be great to automate the whole thing using path names, but I'll save that for later. Any help would be appreciated. Thank You.

 

[RandyD123]

I thought this would work, but no go!!

Sub CopyPasteToAnotherActiveSheet()
    'Copy data from source sheet
    Sheets("MR").Range("N5:O41").Copy
    'Activate destination sheet
    Sheets("Sheet1").Activate
    'Select destination range
    Range("D11:E47").Select
    'Paste in the destination
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

It errors out on "Sheets("MR").Range("N5:K41).Copy