Copy data into the right tab based on text in another file.

[SandsB]

I have a 2 files already open

In file #1 , cell B3 contains a date/time: 5/4/2019 12:30:00 AM
I want the data in cells B3:I40 of file #1 to be copied into the same cells in the second file and into a tab named after the date found in file #1 .

So if B3 of File #1 contains 5/4/2019 12:30:00 AM, the data in B3:I40 is copied into file #2 in tab 05-04.
If B3 of File #1 contains 5/5/2019 12:30:00 AM, the data in B3:I40 is copied into file #2 in tab 05-05. etc.

Not sure how to do it

 

[DanteAmor]

Change the data in red for your information

Option Explicit
Sub Copy_Data()
    Dim l1 As Workbook, l2 As Workbook, h1 As Worksheet, s As Worksheet
    Dim d As String, m As String, wName As String, exists As Boolean
    
    Application.ScreenUpdating = False
    Set l1 = ThisWorkbook
    Set h1 = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")   'Origin
    Set l2 = Workbooks("[COLOR=#ff0000]file2.xlsx[/COLOR]")    'Book destination. It must be open
    
    If h1.Range("B3").Value = "" Or Not IsDate(h1.Range("B3").Value) Then
        MsgBox "Enter a date"
        Exit Sub
    End If
    exists = False
    d = Format(Day(h1.Range("B3").Value), "00")
    m = Format(Month(h1.Range("B3").Value), "00")
    wName = m & "-" & d
    For Each s In l2.Sheets
        If s.Name = wName Then
            exists = True
            Exit For
        End If
    Next
    If exists = False Then
        MsgBox "The sheet does not exist : " & wName
        Exit Sub
    End If
    h1.Range("B3:I40").Copy
    l2.Sheets(wName).Range("B3").PasteSpecial Paste:=xlPasteValues
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    MsgBox "Data copied"
End Sub

 

[SandsB]

Flawless. Really slick - thank you

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.