need help please....

[Psycho0426]

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 & 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.

 

[Bob Phillips]

First 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)

 

[broro183]

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 & use a find & replace in VBE to
change any use of "activesheet" in your code to "ws":

Sub UnprotectClearReprotect()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
'enter your code here
Next ws
End Sub
MsgBox "all done "
End Sub


For your sort issue, go to cell A1, [ctrl+shift+end] (this is not the
full row but should select all cells upto & 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 "sort based on last
name, then first name, then MI".

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Psycho0426]

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("E2").Select
Selection.ClearContents
Range("E3").Select
Selection.ClearContents
Range("E4").Select
Selection.ClearContents
Range("E5").Select
Selection.ClearContents
Range("G10").Select
Selection.ClearContents
Range("G11").Select
Selection.ClearContents
Range("G13").Select
ActiveCell.FormulaR1C1 = "Bill G."
Range("B10").Select
Selection.ClearContents
Range("B11").Select
Selection.ClearContents
Range("B12").Select
Selection.ClearContents
Range("B13").Select
Selection.ClearContents
Range("B14").Select
Selection.ClearContents
Range("B15").Select
Selection.ClearContents
Range("B16").Select
Selection.ClearContents
Range("B17").Select
Selection.ClearContents
Range("B18").Select
Selection.ClearContents
Range("B19").Select
Selection.ClearContents
Range("B20").Select
Selection.ClearContents
Range("B21").Select
Selection.ClearContents
Range("B22").Select
Selection.ClearContents
Range("B23").Select
Selection.ClearContents
Range("B24").Select
Selection.ClearContents
Range("B25").Select
Selection.ClearContents
Range("B26").Select
Selection.ClearContents
Range("B27").Select
Selection.ClearContents
Range("B28").Select
Selection.ClearContents
Range("B29").Select
Selection.ClearContents
Range("B30").Select
Selection.ClearContents
Range("B31").Select
Selection.ClearContents
Range("B32").Select
Selection.ClearContents
Range("B33").Select
Selection.ClearContents
Range("B34").Select
Selection.ClearContents
Range("B35").Select
Selection.ClearContents
Range("B36").Select
Selection.ClearContents
Range("B37").Select
Selection.ClearContents
Range("B38").Select
Selection.ClearContents
Range("B39").Select
Selection.ClearContents
Range("B40").Select
Selection.ClearContents
Range("B41").Select
Selection.ClearContents
Range("B42").Select
Selection.ClearContents
Range("B43").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-6
Range("C10").Select
Selection.ClearContents
Range("C11").Select
Selection.ClearContents
Range("C12").Select
Selection.ClearContents
Range("C13").Select
Selection.ClearContents
Range("C14").Select
Selection.ClearContents
Range("C15").Select
Selection.ClearContents
Range("C16").Select
Selection.ClearContents
Range("C17").Select
Selection.ClearContents
Range("C18").Select
Selection.ClearContents
Selection.ClearContents
Range("C19").Select
Selection.ClearContents
Range("C20").Select
Selection.ClearContents
Range("C21").Select
Selection.ClearContents
Range("C22").Select
Selection.ClearContents
Range("C23").Select
Selection.ClearContents
Range("C24").Select
Selection.ClearContents
Range("C25").Select
Selection.ClearContents
Range("C26").Select
Selection.ClearContents
Range("C27").Select
Selection.ClearContents
Range("C28").Select
Selection.ClearContents
Range("C29").Select
Selection.ClearContents
Range("C30").Select
Selection.ClearContents
Range("C31").Select
Selection.ClearContents
Range("C32").Select
Selection.ClearContents
Range("C33").Select
Selection.ClearContents
Range("C34").Select
Selection.ClearContents
Range("C35").Select
Selection.ClearContents
Range("C36").Select
Selection.ClearContents
Range("C37").Select
Selection.ClearContents
Range("C38").Select
Selection.ClearContents
Range("C39").Select
Selection.ClearContents
Range("C40").Select
Selection.ClearContents
Range("C41").Select
Selection.ClearContents
Range("C42").Select
Selection.ClearContents
Range("C43").Select
Selection.ClearContents
Range("B48").Select
Selection.ClearContents
Range("B49").Select
Selection.ClearContents
Range("B50").Select
Selection.ClearContents
Range("B51").Select
Selection.ClearContents
Range("B52").Select
Selection.ClearContents
Range("B53").Select
Selection.ClearContents
Range("B54").Select
Selection.ClearContents
Range("B55").Select
Selection.ClearContents
Range("B56").Select
Selection.ClearContents
Range("B57").Select
Selection.ClearContents
Range("B58").Select
Selection.ClearContents
Range("C48").Select
Selection.ClearContents
Range("C49").Select
Selection.ClearContents
Range("C50").Select
Selection.ClearContents
Range("C51").Select
Selection.ClearContents
Range("C52").Select
Selection.ClearContents
Range("C53").Select
Selection.ClearContents
Range("C54").Select
Selection.ClearContents
Range("C55").Select
Selection.ClearContents
Range("C56").Select
Selection.ClearContents
Range("C57").Select
Selection.ClearContents
Range("C58").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-36
Range("A1").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: http://www.excelforum.com/attachment.php?postid=4527 |
+-------------------------------------------------------------------+

 

[Bob Phillips]

Replace it.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[broro183]

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, & choosing Format - Protection) so you should test
this on a copy of your workbook first & make sure that only the cells
you want to clear have no tick in the "locked" 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 &
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 "'*").
Other commented lines:
'** this line is straight from your macro & 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("E2:E5,G10:G11,B10:C43,B48:C58").ClearContents '*
sh.Range("G13").Value = "Bill G." '**
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 Sub


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

 

[Psycho0426]

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.

 

[taylorj]

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)

 

[Jonas Lindh]

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 "ActiveWorkbook.Sheets.Count" 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("A1").Activate
Do
If ActiveCell.Locked = False Then
ActiveCell.ClearContents
End If
R = ActiveCell.Row
ActiveCell.Next.Activate
Loop Until ActiveCell.Row < R
Next N
End Sub

Best regards Jonas Lindh

 

[broro183]

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 & 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 &
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:
http://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):
http://www.cpearson.com/excel/optimize.htm
http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm
www.decisionmodels.com

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

 

[Jonas Lindh]

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 "ActiveWorkbook.Sheets.Count" 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("A1").Activate
Do
If ActiveCell.Locked = False Then
ActiveCell.ClearContents
End If
R = ActiveCell.Row
ActiveCell.Next.Activate
Loop Until ActiveCell.Row < R
Next N
End Sub

Best regards Jonas Lindh