Writing a macro that pulls data from a specific worksheet

Is there a way to use the count function in VBA to pull data from the 8th worksheet in a workbook? I can't use the specific name of the worksheet because its liable to change from time to time. As always, thanks in advance for the help.

The following earlier post on this forum describes sheet naming conventions.

http://www.excelforum.com/showthread.php?t=643863

If by the 8th sheet you mean sheet tabs counting from the left, then use the Sheets(8) syntax with e..g.

Please Login or Register  to view this content.

No, it's not being stupid, the syntax Microsoft chose is not particularly intuitive in my opinion and I'm not suprised it trips people up - as indeed it has with me on several occasions.

You need to distinguish between the sheet names and indexes as each one is added to the workbook, and the order of the sheet tabs that exist at any one time - and of course these can be moved around.

In the first case, as sheets are added, they are given an incremental index number, and each new sheet is given the default name Sheet1, Sheet2 Sheet3 etc. This remains as a VBA reference whatever name you subsequently change it to, Thats why in the VBE you see both the Sheet Index 'name' and the tab name. So for instance if you add three sheets and name them A, B, C as you add them, you can subsequently refer to them in code as either Sheet1, Sheet2, Sheet3, or Sheets("A"), Sheets("B"), Sheets("C").

Now if you make the 'B' sheet the last sheet, i.e. the third from the left, if you use code like Sheets(3).Activate, it will be the 'B' sheet that's activated, and not the third sheet that happened to be added to the workbook.

The subtle distinction is between 'Sheet3.Activate' and 'Sheets(3).Activate'

HTH

Ahh thats really good to know, thanks guys!