Excel macro - Find different filenames

[QCube680]

Hi there,

So I'm trying to create a tool used for work purposes, every time we download a file from our server, it is labelled as "USCOTRN" - however, when multiple of these files are downloaded, they automatically rename to "USCOTRN (1)" and go up incrementally - depending on the number of times the file has been downloaded.

So my question is, I want to create a macro that when "USCTORN" is open, the macro automatically moves the worksheet to the new workbook, and formats the data - I have the macro working and formatting correctly, what I can't seem to get around is a way of different file names.

The VBA shows below,

Spoiler

Sub USCOTRNCopy()
'
' USCOTRNCopy Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Windows("USCOTRN.csv").Activate
Sheets("USCOTRN").Select
Sheets("USCOTRN").Move After:=Workbooks("USCOTRNMaster.xlsm").Sheets(1)
Sheets("USCOTRN").Select

Where "Windows ("USCOTRN.csv").Activate is placed, is there a workround where the macro automatically finds excel workbooks that are open, and uses that spreadsheet to run the macro and format the data?

Many thanks for any help you can give!

[#2]

@QCube680 

You may try something like this...

Sub USCOTRNCopy()
Dim wbDest      As Workbook
Dim wbSource    As Workbook
Dim wb          As Workbook
Dim wsSource    As Worksheet

Set wbDest = ThisWorkbook

For Each wb In Workbooks
    If wb.Name Like "USCOTRN*.csv" Then
        Set wbSource = wb
        Exit For
    End If
Next wb

If Not wb Is Nothing Then
    Set wsSource = wbSource.Worksheets("USCOTRN")
    wsSource.Move after:=wbDest.Worksheets(1)
End If

End Sub