How to code a macro to activate a workbook with a wildcard in the title

[processcker]

My macro currently pulls the data I need out of a workbook "week of 7-21 to 7-27.xlsx". I want it to look to pull the data from any workbook that has a similar name (i.e. "week of **-** to **-**.xlsx).

Here is the code I have:

Please Login or Register  to view this content.

Any help would be appreciated.
Thank you.

[Leith Ross]

Hello processcker,

I have converted your macro from a "macro recorder" version to more robust VBA code. Unless there is a specific reason you need to find the last cell by starting at the top and going down, it is a better practice to start at the bottom and come up. I left this part alone since I don't know the reason. It could be it is what the macro recorder inserted.

Please Login or Register  to view this content.

[xladept]

Hi processcker,

I was trying to post this simultaneously with Leith's

Please Login or Register  to view this content.

[processcker]

Thank you Leith. In this case, I do need to find the last cell from the top, but there are some times I have started from the top, but could more easily have found the last cell from the bottom. I just don't know how.

I tried what you gave me, but it didn't get past setting the destination workbook.

I am very unfamiliar with VBA code, so I could have just done something wrong, but prior to this, the macro ran.

Thank you for the input. I didn't realize I was doing this wrong from the start. I'll keep working at it.

[xladept]

If Leith's code didn't work then my code probably doesn't -but did you try it?

[Leith Ross]

Hello processcker,

Did the macro display the message or did Excel? If Excel did then the macro stopped due to a system error. To help you further, I would need to know the error code number and the line in the macro where it stopped.

[processcker]

xladept,
I'm sorry...this didn't work either. For starters, the source workbook is from a temporary drive, so I really just want to work from open workbooks only.

I'm sure your code is good, but as Leith pointed out, I am going from a modified recording as opposed to true VBA code, so the issue really could be me and not what you provided.

I do appreciate what you posted. I tried it and it certainly challenges me to think differently. Thank you.

[processcker]

The macro displayed an error requiring debugging. I'm afraid I'm just too new at this to even understand where I've messed up. I'm sorry for wasting your time.

[Leith Ross]

Hello processcker,

Which line of code is highlighted?

[xladept]

This will dump the non-productive book names at the end of the line - I think

Please Login or Register  to view this content.

[processcker]

Thank you, with all of your help, I think I've finally got it working.

[xladept]

You're welcome!