selecting cells in a ws loop

jerry12302

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

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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
432
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,351
Office Version
  1. 365
Platform
  1. Windows
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,351
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
432
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.
 

Forum statistics

Threads
1,171,534
Messages
5,876,060
Members
433,172
Latest member
Mohamed Hamada 22

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