Forum Discussion

QCube680's avatar
QCube680
Copper Contributor
Jun 19, 2022

Excel macro - Find different filenames

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!

Excel
Macros and VBA
Training
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Jun 20, 2022

    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

Resources