Jun 19 2022 10:31 AM
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,
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!
Jun 19 2022 06:09 PM
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