VBA to select partial filename and go back and forth between two files to copy paste

[jordanburch]

Hi All,
I have the below. I cut off some of the code for times sake. I have two files that I go in and select a tab and then copy and then select the other file with a similar tab name and paste. I just need the TV_SMB to be variable because the file name will change by day so basically want it to look for TV_SMB* and then copy and then always paste into MC combine(never changes). I cant get the syntax correct. Please help!

Sub copypastedata()




    Range("A1:E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("TV_SMB0300E255 Macro 02.28.2024.xlsm").Activate
    Range("E24").Select
    Sheets("CB 1010").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("combine files.xlsm").Activate
    Sheets("CB 1110").Select
    Range("A1:C1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TV_SMB0300E255 Macro 02.28.2024.xlsm").Activate
    Sheets("CB 1110").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("SMB0211").Select
    Windows("combine files.xlsm").Activate
    Sheets("SMB0211").Select
    Range("A1:K1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TV_SMB0300E255 Macro 02.28.2024.xlsm").Activate
    Range("A1").Select

 

[shknbk2]

Put this at the beginning of your code. Change all of the "Windows("TV_SMB0300E255 Macro 02.28.2024.xlsm").Activate" to "w.Activate".

    Dim w As Window
    
    For Each w In Application.Windows
        If InStr(1, w.Caption, "TV_SMB") > 0 Then
            Exit For
        End If
    Next
    
    If w Is Nothing Then
       Exit Sub
    End If