selecting cells in a ws loop

jerry12302

Active Member
Joined
Apr 18, 2005
Messages
407
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>
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
Try this
Code:
            Ws.Activate
           Range("C5").Select

Why do you want the worksheet placement in a macro?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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..

Code:
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

Active Member
Joined
Apr 18, 2005
Messages
407
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Jerry

Try this.
Code:
Application.Goto ws.Range("A1"), Scroll:=True
Application.Goto ws.Range("C5"), Scroll:=False
Or this.
Code:
Application.Goto ws.Range("B107"), Scroll:=True
Application.Goto ws.Range("G127"), Scroll:=False
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Jerry

No problem.:)

But I have to echo the other guys sentiments.

Why are you doing this?
 

jerry12302

Active Member
Joined
Apr 18, 2005
Messages
407
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top