Copy/paste data from one workbook to another but workbook name variable

I want to create a macro that will copy/paste data from one workbook to another. The problem is that the name of one of the workbooks changes on a daily basis. Is there anyway to write the code so that it will refer to a workbook that has certain words? For instance one something like this

Please Login or Register  to view this content.

Don't know if this will help or not?

Please Login or Register  to view this content.

JBeaucaire, how would I program the macro to open the workbook if the name is always different? I usually get emailed the spreadsheet which is only different because of the date.

John Davis, your solution works for me. The only thing is that I would have to use this macro for each step of copy and pasting. I would rather define a variable like JBeaucaire suggested. I tried programing this macro, but it won't run. It combines your idea with defining a variable instead of selecting the sheet. It errors when I define HAFA_Bank as the workbook.

Please Login or Register  to view this content.

Maybe:

Please Login or Register  to view this content.

I noticed your reply in post #4 that only the date will change then if you use Jerry's method maybe this variation, which open a workbook with todays date (depending on the format used)

Please Login or Register  to view this content.

I'm going to play with your solution this morning. Problem with the date idea is that it is always referring to the last business day, not the current day. This gets messy when taking into account weekends. I like the solution of Left(wb.name, 17) = "HAFA QA Reporting". I just want to define the workbook as a new variable so that I can switch back and forth throughout the macro easily without a lot of code.

Post# 5 sets a variable for Sheet1 in Workbook(HAFA QA....). If that Sheet has the same name each day, and you'll be copying/pasting from or too that specific sheet, then you could use that variable (ie. wsHAFA) throughout your code. It will refer back to Workbook(HAFA..).wsHAFA each time.

Looks like I was defining the variable in the wrong order before. I need to put the variable = activesheet instead. I think your solution could work great. I just need time to try it.

Wow. I'm really gonna have to play with this. I wish work would slow down so that I could perfect my processes.

I have had success using your suggestions in some shorter macros, so thanks.

At the moment I'm trying to use this code to look from the right instead of the left. Any idea why this wouldn't be working for me.

Please Login or Register  to view this content.

Totally forgot about .xlsx! Thanks!