Macro inconsistency

[igdent]

Hi all,
I am running a vba that copies from other workbooks and pastes into ThisWorkbook with offsets etc. There are 10 workbooks altogether and at the end of each run the vba is coded to return each workbook to "Sheet1". My problem is that the returning to Sheet1 works in 9 of the workbooks but does not work in the tenth. I took just that piece of code and pasted it into a module on the offending workbook and tried running it separately but it still does not work, yet works in the others.
This is the code (which I actually got from somewhere on this forum if I recall correctly):

Sub GoToFirstSheet()
On Error Resume Next
Sheets(1).Select
End Sub

But somehow I think the problem must be in the book itself. What could stop a book from running such a code without errors etc.?
Thanks in advance.

 

[StephenCrump]

You haven't said what does not work means.

It's also not clear what you mean by Sheet1? Do you mean the worksheet with name "Sheet1"?

Sheets(1) is the first sheet in the workbook. It may be called "Sheet1". It may not.

If you always want "Sheet1", you'll need to refer to Worksheets("Sheet1")

 

[igdent]

You haven't said what does not work means.

It's also not clear what you mean by Sheet1? Do you mean the worksheet with name "Sheet1"?

Sheets(1) is the first sheet in the workbook. It may be called "Sheet1". It may not.

If you always want "Sheet1", you'll need to refer to Worksheets("Sheet1")

Hi Stephen, Thanks for your quick reply. No the sheets have particular names, I do want the first sheet in the workbook. In most of my books it works like a charm, gets to the last sheet during the vba then goes back to activate the first sheet ready for the next run. In this particular workbook it simply does nothing, just stays where it is, not even a flicker. It simply doesn't seem to register. The strange thing is that any other vba/macro works fine in that workbook; it's just that one -- which works fine in all the other workbooks. Confusing.

 

[StephenCrump]

Perhaps Sheets(1) is hidden?

Comment out the On Error Resume Next and see what happens.

 

[igdent]

Perhaps Sheets(1) is hidden?

Comment out the On Error Resume Next and see what happens.

That was the perfect answer, Stephen, thank you. It still is not working correctly but it has certainly shown me where the problem is. This time it worked but ended in an error for debugging, and actually went to the last sheet instead. I think I know now. In all the books I reversed the order of the sheets via Enterprise; somehow this particular workbook is still thinking that that last sheet is the first sheet. I think the best thing I can do is to "rebuild" the workbook. It won't take as long as it sounds, then I will load the macro into the new copy and test it out. Thanks again.

 

[StephenCrump]

This time it worked but ended in an error for debugging, and actually went to the last sheet instead. .. somehow this particular workbook is still thinking that that last sheet is the first sheet.

That sounds unlikely. If the code errored, it wouldn't take you to another sheet. Was the last sheet perhaps already active?

What error message did you get? Perhaps: Run time error '1004': Select method of Worksheet class failed?

When you get the error message, what is the value of Sheets(1).Name?

 

[igdent]

That sounds unlikely. If the code errored, it wouldn't take you to another sheet. Was the last sheet perhaps already active?

What error message did you get? Perhaps: Run time error '1004': Select method of Worksheet class failed?

When you get the error message, what is the value of Sheets(1).Name?

Hi Stephen, The message is "Select method of Worksheet class failed". And you're right, the last sheet must have already been active because when I tried it again just now it did not move. I appreciate your help and would like the answer for future reference, but I think there is something wrong in the workbook itself. I used a VBA to copy all the sheets into a new workbook, and the go to first sheet code works fine in the new book. I have not deleted the old one yet because it would be interesting to get to the bottom of it.

 

[Fluff]

What does the msgbox say if you run this?

Sub chk()
MsgBox Sheets(1).Name & vbLf & Sheets(1).Visible
End Sub

 

[igdent]

Old-New
0

Does that imply that it does have something to do with the fact that I reversed the order?

 

[Fluff]

Does that imply that it does have something to do with the fact that I reversed the order?

No, it means that the 1st sheet is hidden, which is why you cannot select it.