Running a macro when the workbook name changes

[Wheelie686]

I have a macro that reads Workbook 1 and puts certain info into Workbook 2. The problem is that Workbook 1 changes its name depending on the week we're in.

Workbook 1's name right now is Consolidated 2012-08-18 to 2012-08-24 V2.xls but that's from August.
Workbook 2's name is Forecast - 20xx-xx-xx.xlsm

So is there a way I can select the Workbook 2 I want instead of having a specific name coded into my macro?

Please Login or Register  to view this content.

I have the following macro that runs ahead of my CHSI macro. This allows me to open the Workbook 1 I want but yet it still wants to read the specific August file.

Please Login or Register  to view this content.

[patel45]

With this code you can select the file to be opened

Please Login or Register  to view this content.

[Wheelie686]

Thank you patel45 but that doesn't do anything different than my GetMeAFile sub does. I've underlined the part of my first macro that comes up when I go to Debug. Would I replace that line with the code you gave me?

Sorry I'm not as good at VB and macros as I need to be lol

[Norie]

Do you want to select the workbook to open?

This code will do that and create a reference to the workbook, wbConsol, which is used throughout the code.

Please Login or Register  to view this content.

[Wheelie686]

I want to select the workbook I'm copying FROM but the workbook I'm copying TO will always remain the same. I used the code you gave me, Norie, and I get "Compile Error: Named argument not found" with "MultiSelect=False) hilighted in yellow

[Norie]

That's a typo, it should be MultiSelect

[Wheelie686]

I was trying to edit my post when I found the typo

It 'sorta' did what it was supposed to. The only problem is that it copied F296-F343 from Book 1 into B2-B49 of Book 2. That's not quite what it should do but the principal of your macro does solve my 'workbook name changes' issue.

[Norie]

Another typo, in the PasteSpecial line 1 should be I.

Please Login or Register  to view this content.

[Wheelie686]

FANTASTIC! It worked exactly the way it should. This has been so frustrating and now it works!

Now that being said, I do have a number of macros which are (instead of CHSI) called BHSI, Repair and IPTV and I thought copying and pasting, while changing CHSI to the appropriate name, would work. Now it does work but it continues to ask me to open the file each time it encounters the right part of the code. Is there a way to write the code without repeating that 'open file' process?

[Norie]

Have you considered combining all the macros into one?

If all that needs to be changed is the worksheet names you could use an array.

Please Login or Register  to view this content.

[Wheelie686]

I have created a macro that calls the macros in the sequence that I want but that's asking me to open the file multiple times.
I also copy/pasted the fantastic macro you gave me, repeating it without the following text and simply changed the CHSI text to the word I wanted. That worked absolutely great except it wants me to Open one last time, which isn't necessary.

Please Login or Register  to view this content.

Don't get me wrong, I can live with the last File Open but it's unecessary.
I'm afraid I'm learning as I go so I'm not at all familiar with an array.

[Norie]

Did you try the last code I posted?

[Wheelie686]

I feel stupid for asking but where do I post it as part of your original code (which again is great)? I know just enough about building macros manually that if it doesn't start with Sub then it's not a macro correct?

[Norie]

Sorry, should have posted the whole thing.

Please Login or Register  to view this content.

[Wheelie686]

Don't be sorry! It worked PERFECTLY! It does exactly what I want it to and I've tried multiple files and they all open, copy and paste exactly the way they should. Thank you Norie.

[Wheelie686]

I have another addition that I want to put into this macro (above from Norie) if possible but I'm not quite sure how to do so without it asking me to continue opening the file I already have open. The code I have now is as follows.

What I would like to do is copy Sheet1!A2 from the 'variable' worksheet and Paste Special Values into Sheet1!A2 of the constant workbook, which is called Forecast - 20xx-xx-xx.xlsm. I do not want it to be part of the array because the array is set up so it repeats the same steps over and over for each sheet. I only want it to do a straight Paste Special Values from 1 cell to 1 cell.

[Norie]

I'm not quite sure why you don't want to use the array.

Don't you want to copy A2 from each worksheet in the array to the corresponding worksheet in the constant workbook?

Please Login or Register  to view this content.

[Wheelie686]

Actually that worked too...thanks again Norie