| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help please....
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. -- Psycho0426 ------------------------------------------------------------------------ Psycho0426's Profile: http://www.excelforum.com/member.php...o&userid=32832 View this thread: http://www.excelforum.com/showthread...hreadid=526317 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help please....
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) "Psycho0426" wrote in message ... 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. -- Psycho0426 ------------------------------------------------------------------------ Psycho0426's Profile: http://www.excelforum.com/member.php...o&userid=32832 View this thread: http://www.excelforum.com/showthread...hreadid=526317 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help please....
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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=526317 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help please....
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 | +-------------------------------------------------------------------+ -- Psycho0426 ------------------------------------------------------------------------ Psycho0426's Profile: http://www.excelforum.com/member.php...o&userid=32832 View this thread: http://www.excelforum.com/showthread...hreadid=526317 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help please....
Replace it.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Psycho0426" wrote in message ... 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 | +-------------------------------------------------------------------+ -- Psycho0426 ------------------------------------------------------------------------ Psycho0426's Profile: http://www.excelforum.com/member.php...o&userid=32832 View this thread: http://www.excelforum.com/showthread...hreadid=526317 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help please....
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").ClearCon tents '* 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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=526317 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help please....
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: http://www.excelforum.com/member.php...o&userid=32832 View this thread: http://www.excelforum.com/showthread...hreadid=526317 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help please....
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: http://www.excelforum.com/member.php...o&userid=32856 View this thread: http://www.excelforum.com/showthread...hreadid=526317 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help please....
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 Psycho0426 Wrote: 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 JaneTry this. 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. -- Jonas Lindh ------------------------------------------------------------------------ Jonas Lindh's Profile: http://www.excelforum.com/member.php...o&userid=31346 View this thread: http://www.excelforum.com/showthread...hreadid=526317 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help please....
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... Psycho0426 Wrote: 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. :) -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=526317 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help please....
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 -- Jonas Lindh ------------------------------------------------------------------------ Jonas Lindh's Profile: http://www.excelforum.com/member.php...o&userid=31346 View this thread: http://www.excelforum.com/showthread...hreadid=526317 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
