Excel macro - Find different filenames

Copper Contributor

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,


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

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!

1 Reply



You may try something like this...


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