Macro that exports data from another application into excel

[bigmike1720]

I am working on trying to write a macro that when you click on my created button in excel the macro is linked to an application. When the application is open I have a pre-created task it runs and it exports the data. Once the data is exported in the application I have to save the file somewhere before I can open it up. Once the file is open I want to copy and paste the data into the same original excel file that has the button. Then I stop recording the macro. So I have gotten this macro to work, but the problem is this. When I have the export file open with the data, and also the original excel file open where I want the data to get pasted the macro works just fine. However, when I close the exported data file and I try to run the macro in the original excel file I get the error message I need to debug. Below is the error I get. The data that I am trying to pull from the application is starting basketball lineups so the data is going to change frequently. In my code below in the red bold is where I am getting the error in my macro when I close the exported excel file and try to run the macro. I believe the error is derived from my macro only referencing the first export file when I wrote the macro. I think I need to find a way for it to be able to reference a new file name every time I run it! I am not even sure if its possible to do what I want it to, but I figured if anyone would know its the pros!

Thank you Everyone for the help!!

Below is my formula.




Sub Macro2()
'
' Macro2 Macro
'

'
Range("H1").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Columns("A:C").Select
Selection.Copy
Windows("MIKEYrateModel_2023_V6_VBA.xlsm").Activate
Range("A3:C3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A3").Select
Application.WindowState = xlNormal
Windows("NBA Starting Lineups _ DraftKings NBA Lineups and Salaries(4).xlsx"). _
Activate
Columns("A:C").Select
Selection.Copy
Windows("MIKEYrateModel_2023_V6_VBA.xlsm").Activate
Application.WindowState = xlNormal
Windows("NBA Starting Lineups _ DraftKings NBA Lineups and Salaries(4).xlsx"). _
Activate
Range("A1:C1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("MIKEYrateModel_2023_V6_VBA.xlsm").Activate
Range("A3").Select
Application.WindowState = xlNormal
Windows("NBA Starting Lineups _ DraftKings NBA Lineups and Salaries(4).xlsx"). _
Activate
Range("A1:C5").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A1:C1007").Select
Application.CutCopyMode = False
Selection.Copy
Windows("MIKEYrateModel_2023_V6_VBA.xlsm").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G3").Select
End Sub