Referencing Sheet Code name

Hi All,

I have a regular excel extract that im obtaining, however the sheet name is never the same, it is a reference of the system run number "report1442310827144"

Im wanting to reference the sheet and from what i have seen in forums is that you can use the sheet code name. In this case it is "sheet1".

However when i go to reference the sheet code name, i seem to have an issue

My original code is below:-

Please Login or Register  to view this content.

Any help would be greatly appreciated!

Originally Posted by TMS

Please Login or Register  to view this content.

Regards, TMS

Thanks TMS, however when i run the code and replace the Worksheets("report1442310827144") with sheet1.codename i get an error message '438' Object doesnt support this property or method.

Is there something i am doing wrong?

Cheers,
RJNC

Originally Posted by romperstomper

The code must also be in the same workbook to use the codename directly, or you must set a reference to the target workbook from the code workbook. You can also loop through all the sheets and check the codename form another workbook.

Thanks reomperstomper, that is the issue then, im referencing a workbook that i require the codename from, is it achievable then? This is my full code.

Please Login or Register  to view this content.

Originally Posted by TMS

Please Login or Register  to view this content.

It all depends what you know about the structure of the workbook and what the sheet names, and code names might be.

You can loop through the sheets to determine what sheet names are present. For example:

Please Login or Register  to view this content.

Regards, TMS

Thanks TMS for your help on this one, the full code is below apologies it would have probably been easier to do that at the get go to understand the structure. I am referencing a sheet in another workbook. Again any help would be really appreciated, I just cant seem to get it right.

Please Login or Register  to view this content.

Originally Posted by TMS

What is the structure of the workbook you are opening? How many worksheets are there? If it's just one, you can use Sheets(1) without needing to refer to the sheet name or sheet code name. If there's more than one, you can loop through them checking the sheet name, or part of the sheet name. What does the sheet name look like? Is there any part uniquely identifiable?

Perfect! Thanks for the solution and the assistance TMS, works well.