select A4 in all sheets VBA

Paul B

Well-known Member
Joined
Feb 15, 2002
Messages
577
I want to end up with A4 being selected in all the worksheets, not necessary in sheets total and Dates but it is ok, Is there anyway besides the way I am doing it below, this way I have to know what sheets are in the workbook. Can this be done for all the sheets without them being named? I have tried ws.Range("A4").Activate and ws.Range("A4").Select but this does not work. Thanks, Excel ‘97

Sub Clear_Sheet()
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Total" And ws.Name <> "Dates" Then
ws.Range("A4:C250").ClearContents
ws.Range("A4") = "Start Here"
Sheets(Array("Dee", "Brian", "Joe", "Rusty", "Ken", "David H", _
"Wesley", "Randy", "David S", "Jack", "Scott", "Tony", "Charlie", "Kelvin")).Select
Sheets("Dee").Activate
Range("A4").Select
End If
Next ws
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Howdy Paul,

Haven't tried this in '97, but I know selecting ranges the way you're describing is problematic in xl 2000 as well. Try using application.goto like below, a little more robust:

<pre>
Sub Clear_Sheet()
Dim ws As Worksheet, acSt As Integer
acSt = ActiveSheet.Index
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Total" And ws.Name <> "Dates" Then
ws.[A4:C250].ClearContents
ws.[A4] = "Start Here"
Application.Goto ws.[A4]
End If
Next ws
Sheets(acSt).Activate
Application.ScreenUpdating = True
End Sub</pre>

Have a good one.
 
Upvote 0
Hi,

Your routine should work, although you appear to have some extra code that you can remove.

Does this do what you need?

<pre>Sub Clear_Sheet()
Dim ws As Worksheet


For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Total" And ws.Name <> "Dates" Then
ws.Select
Range("A4:C250").ClearContents
Range("A4").Select
End If
Next ws

End Sub</pre>
 
Upvote 0
Nate, I had tried ws. Application.Goto(“A4”), got the ws in the wrong place, also I did not know about using [A4] instead of Range("A4") that will save a little typing. Thanks
This message was edited by paul b on 2002-08-28 18:59
 
Upvote 0

Forum statistics

Threads
1,223,368
Messages
6,171,682
Members
452,416
Latest member
johnog

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
Back
Top