selecting cells in a ws loop

[jerry12302]

I can't seem to get this code to select a cell within a ws loop, can someone review this and tell me what's needed?

Thanks

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> test()
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Ws <SPAN style="color:#00007F">In</SPAN> Worksheets
        <SPAN style="color:#00007F">If</SPAN> Ws.Index <> 1 <SPAN style="color:#00007F">Then</SPAN>
            Ws.Unprotect
            Worksheets(1).Range("D36:D37").Copy Ws.Range("D36")
            Ws.Protect
            <SPAN style="color:#007F00">'This is working perfectly until the next line where it crashes:</SPAN>
            Ws.Range("C5").Select
            <SPAN style="color:#007F00">'How do I get the cellpointer at C5 (with A1 being at the upper left</SPAN>
            <SPAN style="color:#007F00">'corner) in each worksheet?</SPAN>
            <SPAN style="color:#007F00">'</SPAN>
            <SPAN style="color:#007F00">'Alternately, what if I wanted B107 in the upper left corner and the</SPAN>
            <SPAN style="color:#007F00">'cellpointer at G127?</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> Ws
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[texasalynn]

Try this

            Ws.Activate
           Range("C5").Select

Why do you want the worksheet placement in a macro?

 

[Jonmo1]

Question. Why do you need to select the cell? it is not necessary to select cells to manipulate them in VBA code.

However, if it's strictly for appearance, of coarse you can do it.

I think the reason it's not working for you is because you haven't ACTIVATED each sheet during the loop. The sheet must be active in order to select a cell on that sheet. So Add

Ws.Activate

to the beginning of the loop..

Sub test()
    For Each Ws In Worksheets
        If Ws.Index <> 1 Then
            Ws.Unprotect
            Ws.Activate
            Worksheets(1).Range("D36:D37").Copy Ws.Range("D36")
            Ws.Protect
            'This is working perfectly until the next line where it crashes:
            Ws.Range("C5").Select
            'How do I get the cellpointer at C5 (with A1 being at the upper left
            'corner) in each worksheet?
            '
            'Alternately, what if I wanted B107 in the upper left corner and the
            'cellpointer at G127?
        End If
    Next Ws
End Sub

 

[jerry12302]

Thanks, it's partly working. The purpose is so the user will see a certain area of the worksheet (where I want them) as they scroll through them when they open the file, so they don't have to click on the sheet and then manually navigate to where I want them to be.

Although the desired cell is selected, which is great, A1 (or whatever I may choose) is still not necessarily the upper left cell when they click on the sheet.

Is there a way to also specify this?

 

[Norie]

Jerry

Try this.

Application.Goto ws.Range("A1"), Scroll:=True
Application.Goto ws.Range("C5"), Scroll:=False

Or this.

Application.Goto ws.Range("B107"), Scroll:=True
Application.Goto ws.Range("G127"), Scroll:=False

 

[jerry12302]

Thanks Norie, that's perfect.

 

[Norie]

Jerry

No problem.

But I have to echo the other guys sentiments.

Why are you doing this?

 

[jerry12302]

The purpose is so the user will see a certain area of the worksheet (where I want them) as they click on them, so they don't have to click on the sheet and then manually navigate to where I want them to be.

There may be 100 different areas on each sheet, depending on the task I want them to do. I'm just making it easier by having the cellpointer already where I want it in every sheet.