Need help with using variables for file names

[nsrii]

Hi all,

I'm new to macros and to the forum. I'm sure this is incredibly simple and I'm over-thinking it, but here it goes...

What I'm trying to do: I have two open spreadsheets (containing one worksheet each), I need to have all hyphens replaced by zeros, I need to delete one column from one of the open spreadsheets, and then the content of each spreadsheet needs to be copied and pasted into my main template, which is also opened.

The problem I'm running into is that each of the spreadsheets has a variable name. The variable names on the spreadsheets have constant prefixes (ie teamAux and teamSummary), but the rest of the file name is randomly generated based on the time it was created--see the code sample below. Additionally, the automation process also names the worksheets for each of the spreadsheets to the same name as the file name.

I'll need to run this macro multiple times a day, so the randomly generated file names are causing me a headache, because I don't know how to set the macro up to just make the hyphen to zero replacement, column deletion, and copy/paste happen without calling the files by their specific name.

Is there an easy way to set the macro up to make the changes and grab the info from each spreadsheet based on the static section of the name (teamAux and teamSummary) using some sort of variable? What would be the most efficient way to do this?

Any help or suggestions would be greatly appreciated!! Thank you all for your time

Here's the code from the macro:

Sub Macro4()
'
' Macro4 Macro
'

'
    Windows("teamSummary1354876770-20121207-0252.csv").Activate
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    Range("M2").Activate
    Selection.Copy
    Windows("Hourly Metrics Template.xlsm").Activate
    Sheets("Insert Data team summary ").Select
    Range("A1").Select
    ActiveSheet.Paste
    Selection.Replace What:="-", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Windows("teamAux1354876773-20121207-0252.csv").Activate
    Cells.Select
    Range("O5").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Hourly Metrics Template.xlsm").Activate
    Sheets("insert data aux times").Select
    Range("A1").Select
    ActiveSheet.Paste
    Selection.Replace What:="-", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub