software

I have a problem that I can't seem to solve. I'm trying to create a
macro, or find some othe way of automating the unprotecting the
worksheet, clearing of all non-protected cells on the worksheet,
re-protect the worksheet with no password, and move on to the next
worksheet in the file and do it again to the end. The file (workbook?)
is 32 worksheets long, 1 for every day amp; 1 for monthly totals. My
attempts to record a macro have blown up in my face miserably. They
refuse to process more than one page even when I select them all. I'm
sure excel can do this, but I'm self-taught so there's a lot I don't
know how to do yet. I'm using excel 2003.

Also, on a different worksheet I'm trying to find a way to sort
entire rows based on customers names. Example:Account # Last MI First
----------------------------
xxxxxxxxxx Public Q John
xxxxxxxxxx Blow B Joe
xxxxxxxxxx Doe A Jane

*******************Sort To************************

Account # Last MI First
----------------------------
xxxxxxxxxx Blow B Joe
xxxxxxxxxx Doe A Jane
xxxxxxxxxx Public Q John

I would like to sort based on last name, then first name, then MI,
but I can't find a way to keep the entire row together. Anyone have any
ideas??? I don't have a clue how to do this. Thank you for any
assistance.--
Psycho0426------------------------------------------------------------------------
Psycho0426's Profile: www.excelforum.com/member.php...oamp;userid=32832
View this thread: www.excelforum.com/showthread...hreadid=526317First bit

Sub ClearCells()
Dim sh As Worksheet
Dim cell As Range

For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect
For Each cell In sh.UsedRange
If Not cell.Locked Then
cell.ClearContents
End If
Next cell
sh.Protect
Next sh

End Sub--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Psycho0426quot; gt; wrote
in message ...
gt;
gt; I have a problem that I can't seem to solve. I'm trying to create a
gt; macro, or find some othe way of automating the unprotecting the
gt; worksheet, clearing of all non-protected cells on the worksheet,
gt; re-protect the worksheet with no password, and move on to the next
gt; worksheet in the file and do it again to the end. The file (workbook?)
gt; is 32 worksheets long, 1 for every day amp; 1 for monthly totals. My
gt; attempts to record a macro have blown up in my face miserably. They
gt; refuse to process more than one page even when I select them all. I'm
gt; sure excel can do this, but I'm self-taught so there's a lot I don't
gt; know how to do yet. I'm using excel 2003.
gt;
gt; Also, on a different worksheet I'm trying to find a way to sort
gt; entire rows based on customers names. Example:
gt;
gt;
gt; Account # Last MI First
gt; ----------------------------
gt; xxxxxxxxxx Public Q John
gt; xxxxxxxxxx Blow B Joe
gt; xxxxxxxxxx Doe A Jane
gt;
gt; *******************Sort To************************
gt;
gt; Account # Last MI First
gt; ----------------------------
gt; xxxxxxxxxx Blow B Joe
gt; xxxxxxxxxx Doe A Jane
gt; xxxxxxxxxx Public Q John
gt;
gt; I would like to sort based on last name, then first name, then MI,
gt; but I can't find a way to keep the entire row together. Anyone have any
gt; ideas??? I don't have a clue how to do this. Thank you for any
gt; assistance.
gt;
gt;
gt; --
gt; Psycho0426
gt;
gt;
gt; ------------------------------------------------------------------------
gt; Psycho0426's Profile:
www.excelforum.com/member.php...oamp;userid=32832
gt; View this thread: www.excelforum.com/showthread...hreadid=526317
gt;

Hi,

For your macro problem, I'm assuming the code you have works well on
the active sheet (if not, let us know what is causing problems). IF so,
try putting your code into this macro amp; use a find amp; replace in VBE to
change any use of quot;activesheetquot; in your code to quot;wsquot;:

Sub UnprotectClearReprotect()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
'enter your code here
Next ws
End Sub
MsgBox quot;all done :-)quot;
End SubFor your sort issue, go to cell A1, [ctrl shift end] (this is not the
full row but should select all cells upto amp; including the last column
with info in them), [alt d s] (ie Data - Sort), click yes for your
header row, then select the sort options to get quot;sort based on last
name, then first name, then MIquot;.

hth
Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=526317
My macro for the first problem is below. I decided to zip up and
include my file with this post. Broro183, your solution to my second
problem was right on. I was soooo close. I was only highlighting the
columns the sort would be based upon, but otherwise I was there.
Grrrrr. As far as your or Bob Phillips code. do I replace mine with
it, or insert it somewhere??? Thanks. My macro follows (I hope):
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/01/2006 by Front Desk2
'
' Keyboard Shortcut: Ctrl h
'
ActiveSheet.Unprotect
Range(quot;E2quot;).Select
Selection.ClearContents
Range(quot;E3quot;).Select
Selection.ClearContents
Range(quot;E4quot;).Select
Selection.ClearContents
Range(quot;E5quot;).Select
Selection.ClearContents
Range(quot;G10quot;).Select
Selection.ClearContents
Range(quot;G11quot;).Select
Selection.ClearContents
Range(quot;G13quot;).Select
ActiveCell.FormulaR1C1 = quot;Bill G.quot;
Range(quot;B10quot;).Select
Selection.ClearContents
Range(quot;B11quot;).Select
Selection.ClearContents
Range(quot;B12quot;).Select
Selection.ClearContents
Range(quot;B13quot;).Select
Selection.ClearContents
Range(quot;B14quot;).Select
Selection.ClearContents
Range(quot;B15quot;).Select
Selection.ClearContents
Range(quot;B16quot;).Select
Selection.ClearContents
Range(quot;B17quot;).Select
Selection.ClearContents
Range(quot;B18quot;).Select
Selection.ClearContents
Range(quot;B19quot;).Select
Selection.ClearContents
Range(quot;B20quot;).Select
Selection.ClearContents
Range(quot;B21quot;).Select
Selection.ClearContents
Range(quot;B22quot;).Select
Selection.ClearContents
Range(quot;B23quot;).Select
Selection.ClearContents
Range(quot;B24quot;).Select
Selection.ClearContents
Range(quot;B25quot;).Select
Selection.ClearContents
Range(quot;B26quot;).Select
Selection.ClearContents
Range(quot;B27quot;).Select
Selection.ClearContents
Range(quot;B28quot;).Select
Selection.ClearContents
Range(quot;B29quot;).Select
Selection.ClearContents
Range(quot;B30quot;).Select
Selection.ClearContents
Range(quot;B31quot;).Select
Selection.ClearContents
Range(quot;B32quot;).Select
Selection.ClearContents
Range(quot;B33quot;).Select
Selection.ClearContents
Range(quot;B34quot;).Select
Selection.ClearContents
Range(quot;B35quot;).Select
Selection.ClearContents
Range(quot;B36quot;).Select
Selection.ClearContents
Range(quot;B37quot;).Select
Selection.ClearContents
Range(quot;B38quot;).Select
Selection.ClearContents
Range(quot;B39quot;).Select
Selection.ClearContents
Range(quot;B40quot;).Select
Selection.ClearContents
Range(quot;B41quot;).Select
Selection.ClearContents
Range(quot;B42quot;).Select
Selection.ClearContents
Range(quot;B43quot;).Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-6
Range(quot;C10quot;).Select
Selection.ClearContents
Range(quot;C11quot;).Select
Selection.ClearContents
Range(quot;C12quot;).Select
Selection.ClearContents
Range(quot;C13quot;).Select
Selection.ClearContents
Range(quot;C14quot;).Select
Selection.ClearContents
Range(quot;C15quot;).Select
Selection.ClearContents
Range(quot;C16quot;).Select
Selection.ClearContents
Range(quot;C17quot;).Select
Selection.ClearContents
Range(quot;C18quot;).Select
Selection.ClearContents
Selection.ClearContents
Range(quot;C19quot;).Select
Selection.ClearContents
Range(quot;C20quot;).Select
Selection.ClearContents
Range(quot;C21quot;).Select
Selection.ClearContents
Range(quot;C22quot;).Select
Selection.ClearContents
Range(quot;C23quot;).Select
Selection.ClearContents
Range(quot;C24quot;).Select
Selection.ClearContents
Range(quot;C25quot;).Select
Selection.ClearContents
Range(quot;C26quot;).Select
Selection.ClearContents
Range(quot;C27quot;).Select
Selection.ClearContents
Range(quot;C28quot;).Select
Selection.ClearContents
Range(quot;C29quot;).Select
Selection.ClearContents
Range(quot;C30quot;).Select
Selection.ClearContents
Range(quot;C31quot;).Select
Selection.ClearContents
Range(quot;C32quot;).Select
Selection.ClearContents
Range(quot;C33quot;).Select
Selection.ClearContents
Range(quot;C34quot;).Select
Selection.ClearContents
Range(quot;C35quot;).Select
Selection.ClearContents
Range(quot;C36quot;).Select
Selection.ClearContents
Range(quot;C37quot;).Select
Selection.ClearContents
Range(quot;C38quot;).Select
Selection.ClearContents
Range(quot;C39quot;).Select
Selection.ClearContents
Range(quot;C40quot;).Select
Selection.ClearContents
Range(quot;C41quot;).Select
Selection.ClearContents
Range(quot;C42quot;).Select
Selection.ClearContents
Range(quot;C43quot;).Select
Selection.ClearContents
Range(quot;B48quot;).Select
Selection.ClearContents
Range(quot;B49quot;).Select
Selection.ClearContents
Range(quot;B50quot;).Select
Selection.ClearContents
Range(quot;B51quot;).Select
Selection.ClearContents
Range(quot;B52quot;).Select
Selection.ClearContents
Range(quot;B53quot;).Select
Selection.ClearContents
Range(quot;B54quot;).Select
Selection.ClearContents
Range(quot;B55quot;).Select
Selection.ClearContents
Range(quot;B56quot;).Select
Selection.ClearContents
Range(quot;B57quot;).Select
Selection.ClearContents
Range(quot;B58quot;).Select
Selection.ClearContents
Range(quot;C48quot;).Select
Selection.ClearContents
Range(quot;C49quot;).Select
Selection.ClearContents
Range(quot;C50quot;).Select
Selection.ClearContents
Range(quot;C51quot;).Select
Selection.ClearContents
Range(quot;C52quot;).Select
Selection.ClearContents
Range(quot;C53quot;).Select
Selection.ClearContents
Range(quot;C54quot;).Select
Selection.ClearContents
Range(quot;C55quot;).Select
Selection.ClearContents
Range(quot;C56quot;).Select
Selection.ClearContents
Range(quot;C57quot;).Select
Selection.ClearContents
Range(quot;C58quot;).Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-36
Range(quot;A1quot;).Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True,
Scenarios:= _
False, AllowFormattingCells:=True,
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True,
AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True,
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True,
AllowFiltering:=True, _
AllowUsingPivotTables:=True
ActiveSheet.EnableSelection = xlNoRestrictions
End Sub
I hope this worked. -------------------------------------------------------------------
|Filename: Audit - Temp.zip |
|Download: www.excelforum.com/attachment.php?postid=4527 |
-------------------------------------------------------------------

--
Psycho0426------------------------------------------------------------------------
Psycho0426's Profile: www.excelforum.com/member.php...oamp;userid=32832
View this thread: www.excelforum.com/showthread...hreadid=526317Replace it.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Psycho0426quot; gt; wrote
in message ...
gt;
gt; My macro for the first problem is below. I decided to zip up and
gt; include my file with this post. Broro183, your solution to my second
gt; problem was right on. I was soooo close. I was only highlighting the
gt; columns the sort would be based upon, but otherwise I was there.
gt; Grrrrr. As far as your or Bob Phillips code. do I replace mine with
gt; it, or insert it somewhere??? Thanks. My macro follows (I hope):
gt;
gt;
gt;
gt; Sub Macro1()
gt; '
gt; ' Macro1 Macro
gt; ' Macro recorded 3/01/2006 by Front Desk2
gt; '
gt; ' Keyboard Shortcut: Ctrl h
gt; '
gt; ActiveSheet.Unprotect
gt; Range(quot;E2quot;).Select
gt; Selection.ClearContents
gt; Range(quot;E3quot;).Select
gt; Selection.ClearContents
gt; Range(quot;E4quot;).Select
gt; Selection.ClearContents
gt; Range(quot;E5quot;).Select
gt; Selection.ClearContents
gt; Range(quot;G10quot;).Select
gt; Selection.ClearContents
gt; Range(quot;G11quot;).Select
gt; Selection.ClearContents
gt; Range(quot;G13quot;).Select
gt; ActiveCell.FormulaR1C1 = quot;Bill G.quot;
gt; Range(quot;B10quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B11quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B12quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B13quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B14quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B15quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B16quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B17quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B18quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B19quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B20quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B21quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B22quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B23quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B24quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B25quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B26quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B27quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B28quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B29quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B30quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B31quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B32quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B33quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B34quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B35quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B36quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B37quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B38quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B39quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B40quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B41quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B42quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B43quot;).Select
gt; Selection.ClearContents
gt; ActiveWindow.SmallScroll Down:=-6
gt; Range(quot;C10quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C11quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C12quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C13quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C14quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C15quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C16quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C17quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C18quot;).Select
gt; Selection.ClearContents
gt; Selection.ClearContents
gt; Range(quot;C19quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C20quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C21quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C22quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C23quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C24quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C25quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C26quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C27quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C28quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C29quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C30quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C31quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C32quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C33quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C34quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C35quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C36quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C37quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C38quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C39quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C40quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C41quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C42quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C43quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B48quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B49quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B50quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B51quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B52quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B53quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B54quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B55quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B56quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B57quot;).Select
gt; Selection.ClearContents
gt; Range(quot;B58quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C48quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C49quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C50quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C51quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C52quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C53quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C54quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C55quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C56quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C57quot;).Select
gt; Selection.ClearContents
gt; Range(quot;C58quot;).Select
gt; Selection.ClearContents
gt; ActiveWindow.SmallScroll Down:=-36
gt; Range(quot;A1quot;).Select
gt; ActiveSheet.Protect DrawingObjects:=False, Contents:=True,
gt; Scenarios:= _
gt; False, AllowFormattingCells:=True,
gt; AllowFormattingColumns:=True, _
gt; AllowFormattingRows:=True, AllowInsertingColumns:=True,
gt; AllowInsertingRows _
gt; :=True, AllowInsertingHyperlinks:=True,
gt; AllowDeletingColumns:=True, _
gt; AllowDeletingRows:=True, AllowSorting:=True,
gt; AllowFiltering:=True, _
gt; AllowUsingPivotTables:=True
gt; ActiveSheet.EnableSelection = xlNoRestrictions
gt; End Sub
gt;
gt;
gt;
gt; I hope this worked.
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: Audit - Temp.zip |
gt; |Download: www.excelforum.com/attachment.php?postid=4527 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; Psycho0426
gt;
gt;
gt; ------------------------------------------------------------------------
gt; Psycho0426's Profile:
www.excelforum.com/member.php...oamp;userid=32832
gt; View this thread: www.excelforum.com/showthread...hreadid=526317
gt;

Hi Bill,

Cool, pleased I could help.

Replace it as Bob says. However, it will clear the contents of all
cells in each sheet which are not locked (you can see this by right
clicking on a cell, amp; choosing Format - Protection) so you should test
this on a copy of your workbook first amp; make sure that only the cells
you want to clear have no tick in the quot;lockedquot; option on the protection
tab (this is what Bob's macro looks at). Fyi, the default value is that
cells are locked so you should be fine if you set up the spreadsheets amp;
only you only unlocked these cells on all the worksheets.
However, if you aren't confident in this, it may be safer to use this
version, as it just clears the contents of the cells listed in your
macro (see the line with quot;'*quot;).
Other commented lines:
'** this line is straight from your macro amp; may have been an unintended
entry of your name in G13 while recording the macro. If so, delete the
whole line.
'*** I have just modified Bob's macro to reflect the options that were
selected in your recorded version - they may all be the default values
in which case they aren't needed (I haven't checked).

Sub ModifiedClearCells()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect
sh.Range(quot;E2:E5,G10:G11,B10:C43,B48:C58quot;).ClearCon tents '*
sh.Range(quot;G13quot;).Value = quot;Bill G.quot; '**
sh.protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True,
AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True '***
sh.EnableSelection = xlNoRestrictions '***

Next sh

End SubHth
Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=526317
Sweet! Thanks, both of you, for the help. Is there someplace online
that I can learn how to write macros from a novice level? I'm trying
to understand what I cut and pasted, but it's way over my head. Thanks
again. --
Psycho0426------------------------------------------------------------------------
Psycho0426's Profile: www.excelforum.com/member.php...oamp;userid=32832
View this thread: www.excelforum.com/showthread...hreadid=526317
Best advice is to record small chunks of macro, then work through the
code step-by-step.
That's how I learnt, but then used Google for more complex stuff
(especially the stuff using collections etc. that the guys have used
above)--
taylorj
------------------------------------------------------------------------
taylorj's Profile: www.excelforum.com/member.php...oamp;userid=32856
View this thread: www.excelforum.com/showthread...hreadid=526317
Hello John
Try this.

It will work provided that there are at least two unprotected cells on
more than one line in each sheet.

You can sustitute quot;ActiveWorkbook.Sheets.Countquot; with 32 if your
described sheets are placed first or are the only existing sheets in
the workbook .

Sub ClearAllUnlockedCellsOnAllSheets()
For N = 1 To ActiveWorkbook.Sheets.Count
R = 0
ActiveWorkbook.Sheets(N).Activate
ActiveWorkbook.Sheets(N).Range(quot;A1quot;).Activate
Do
If ActiveCell.Locked = False Then
ActiveCell.ClearContents
End If
R = ActiveCell.Row
ActiveCell.Next.Activate
Loop Until ActiveCell.Row lt; R
Next N
End Sub

Best regards Jonas Lindh

Psycho0426 Wrote:
gt; I have a problem that I can't seem to solve. I'm trying to create a
gt; macro, or find some othe way of automating the unprotecting the
gt; worksheet, clearing of all non-protected cells on the worksheet,
gt; re-protect the worksheet with no password, and move on to the next
gt; worksheet in the file and do it again to the end. The file (workbook?)
gt; is 32 worksheets long, 1 for every day amp; 1 for monthly totals. My
gt; attempts to record a macro have blown up in my face miserably. They
gt; refuse to process more than one page even when I select them all. I'm
gt; sure excel can do this, but I'm self-taught so there's a lot I don't
gt; know how to do yet. I'm using excel 2003.
gt;
gt; Also, on a different worksheet I'm trying to find a way to sort entire
gt; rows based on customers names. Example:
gt;
gt;
gt; Account # Last MI First
gt; ----------------------------
gt; xxxxxxxxxx Public Q John
gt; xxxxxxxxxx Blow B Joe
gt; xxxxxxxxxx Doe A Jane
gt;
gt; *******************Sort To************************
gt;
gt; Account # Last MI First
gt; ----------------------------
gt; xxxxxxxxxx Blow B Joe
gt; xxxxxxxxxx Doe A JaneTry this.
gt; xxxxxxxxxx Public Q John
gt;
gt; I would like to sort based on last name, then first name, then MI,
gt; but I can't find a way to keep the entire row together. Anyone have any
gt; ideas??? I don't have a clue how to do this. Thank you for any
gt; assistance.--
Jonas Lindh
------------------------------------------------------------------------
Jonas Lindh's Profile: www.excelforum.com/member.php...oamp;userid=31346
View this thread: www.excelforum.com/showthread...hreadid=526317
Hi Bill,
No problem, thanks for the feedback.

re macros at a novice level:
As TaylorJ has mentioned the best way is to play around by recording
chunks of macros amp; breaking down the code (use [F8] to step through
code a line at a time, with your VBE window 1/2 size so you can see
what is happening in Excel). Make use of the [F1] key when needed amp;
search the news groups (you'll learn heaps).
It's always easier to learn when you have a specific goal in mind, but
for general background info have a read of Dave McRitchie's intro:
www.mvps.org/dmcritchie/excel/getstarted.htm (good for
beginners)

Also, some other sources of info for optimising macros/worksheets may
come in handy as you get further into writing macros (it's wise to
learn good habits when you start though):
www.cpearson.com/excel/optimize.htm
www.ozgrid.com/VBA/SpeedingUpVBACode.htm
www.decisionmodels.com

Hth
Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...

Psycho0426 Wrote:
gt; Sweet! Thanks, both of you, for the help. Is there someplace online
gt; that I can learn how to write macros from a novice level? I'm trying
gt; to understand what I cut and pasted, but it's way over my head. Thanks
gt; again. --
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=526317