VBA to use a variable filename

Good Morning all-

I am looking to run a macro against a downloaded file that will always have a different file name and sheet name every day (example Report123456789). The macro needs to take this file format some columns, change the sheet name to Sheet1 and then save as an excel 97-2003 version to a server drive. The format macro I got, but how can i tell VBA that the file and sheet name will be different each time and then save to a specified location. I have tried report*.xls. Also, i want the macro to be in a separate file and use the activate file (with the Report123456789) when i run it from the separate file.


Any ideas?

thanks in advance!

.

I am confused. Post #2 is talking about 3 files? One with the macro, a macro in file two (active) to run a macro in file one, and then the macro makes file three?

You can run a macro from another file by Application.Run.

For the file one macro, post the code that you have between code tags. Click # icon on reply toolbar to insert tags. What is the filename to create?

.
A simple solution might be to have the downloaded file downloaded to a separate folder all by itself. No other files in that one folder. Then code your macro so it opens any file in that folder using the
extension of the downloaded file. That way you don't have to bother with the file name (which is always changing), just the file extension.