Problem activating workbooks and worksheet with variable name

[Jemwig]

Hi

I have 2 open workbooks. I am running a macro from workbook 1 which requires activating the second workbook, selecting data from a worksheet in the second workbook and copying into the first workbook. The second workbook name can vary but always begins with "Ad Views"

I am currently using this code but cannot get it to activate the second workbook to select the data

Please Login or Register  to view this content.

[Norie]

You don't need to activate either workbook.

Also, this will never find the other workbook as 'Ad Views' is not in lower case

Please Login or Register  to view this content.

It's not 100% clear what you are trying to do, mainly because the copy/paste code is inside the loop.

Anyway, try this.

Please Login or Register  to view this content.

PS When posting code can you add code tags - makes it easier to read/understand.

[Alf]

Perhaps you could use the fact that that Excel keeps track of the workbooks it opends and in the order they are opened. So the first workbook you open is Workbook(1) and to activate the second workbook you opens just add the line

Please Login or Register  to view this content.

to your code.

Alf

[Jemwig]

Hi Thanks

I am afrraid that did not work

The name of the workbook is actually "Ad Views" follwed by a variable and does have upper case initial letters in the words.


To clarify, I am using the vba macro in a first workbook to copy a dynamic range from a second workbook and paste into the first workbook. The problem is that the second workbook name could be Ad Views (1) , Ad Views (2) etc. There is only one worksheet in the second workbook.

Thanks

[Norie]

In what way does it not work?

Why are you converting the workbook name to lower case with LCase and then comparing it to a string with upper case letters?

Also, normally when using Like you would include a wildcard.

For example, this will find a workbook with 'Ad Views' at the start of the name.

Please Login or Register  to view this content.

[arlu1201]

Jemwig,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.