macro will not look at active workbook

[long7time]

For some reason my macro has stop working, I have 2 workbooks open in the same excel session. each with its own unique name. The macro looks for a specific name and then copies data from this workbook to the 2nd workbook. For some reason it no longer like the name of the workbook #1 which has not change (as far as I can tell) When I run the macro i get the run time error '9' subscript out of range, when i click on the debug this is what is highlighted and underlined.

Please Login or Register  to view this content.

the cell BP01 has the exact name of the 2nd workbook with no spaces or extra dashes that I can tell. i have removed and retyped in the name to assure all letters and space are correct in this cell.

I am using this formula in cell BP01

=CONCATENATE(BJ1," ",BL2,"-",BM2,"-",BO2,".xls")

BJ1 = name of 2nd workbook
BL2 = Month
BM2 = day
BO2 = year


Any suggestions or how to fix this error?

thanks

[shg]

Please edit your post to add code tags.

[long7time]

This is done now, sorry

[shg]

Thanks.

Try

Please Login or Register  to view this content.

Pathname should specifically NOT include a path - just the workbook name.

And if you want to reference the active workbook, there's no need -- it is, by definition, the activated workbook.

[long7time]

thank you for the input, that was the solution to the problem.

[shg]

You’re welcome. Would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes