How to make same macro run on workbooks with different names

[LoriD]

Hello,

I have tried researching previous posts and can not seem to find anything similar to what I'm asking. I tried recording a macro to automate some tasks I perform every day between two workbooks. While in one I want to go get data from the other. The problem I'm having is the name of the workbooks I want to run this on changes every time I download my data into Excel. The files always start with the same name but the date gets appended to it. Is there a way to modify the macro to just work with the two books I've got open? Any help would be greatly appreciated!

 

[texasalynn]

this is some code that I use to open two files and do work on

sFilename = Application.GetOpenFilename("XLS Files(*.xls),*.xls")
Set wb = Workbooks.Open(sFilename)
sName = ActiveSheet.Name
tFilename = Application.GetOpenFilename("Text Files(*.xls),*.xls")
Set WB2 = Workbooks.Open(tFilename)
tName = ActiveSheet.Name

 

[LoriD]

Thank you for your quick response. The code works great to find my files, but once I've got them open and attempt to run a macro, it has the original filenames in it. I was hoping there was a way to get those specific file names out of it so the macro will just run on whatever two workbooks I've got open.

 

[par60056]

Can you post some of your code? What you want to do is replace the workbook references in the recorded code with references to WB and WB2 which you assign to the workbooks you are using.

If you have the workbooks already open then you can set WB and WB2 by looking at the workbooks collection and looking for workbooks with names that match a pattern.


The way that you use the WB references will depend a bit on what the code is trying to do.