Need help with macro that clears out several different worksheets

[shellecj]

I have created this macro to clear all data from tables on 7 different worksheet tabs; however, the macro aborts if one of the worksheets happens to have been cleared already. Is there a way to avoid this, possibly with an if statement to check if the sheet is empty already (other than header row of course) and if so skip to the next sheet?

Also, I put an onerror goto in the code, with the intent that something went wrong with the macro, that it would skip down to the end and give the error message (rather than the runtime msg with end, abort, etc, which would confuse others when using the macro); however the error msg shows each time, even if the macro was successful? Am I misusing it?

Below is what I have for my code already if someone could take a look at it to make suggestions. Also, if there's anything that could be revised to be more efficient, etc, please advise. I am still very new and learning VBA....Thanks!

Sub Clear_Worksheets()
'
' Clear all worksheets to prepare for importing new data
On Error GoTo Errormessage:
If MsgBox("You are about to clear all data from each tab. Do you want to continue?", vbYesNo + vbExclamation) = vbNo Then
    Exit Sub
   
    Else
    Sheets("Inventory Status").Visible = True
    Sheets("Inventory Status").Unprotect Password:=“password”
    Sheets("Inventory Status").Select
    'ActiveSheet.Unprotect
    'ActiveSheet.ShowAllData
    Columns("A:AF").Hidden = False
    Worksheets("Inventory Status").AutoFilterMode = False
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Columns("L").Hidden = True
    Columns("P:R").Hidden = True
    Columns("Y:AA").Hidden = True
    Range("AC2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(ISNA(VLOOKUP(RC[-26],Wkly_Notes,3,0)),VLOOKUP(RC[-26],Eviction,6,0),VLOOKUP(RC[-26],Wkly_Notes,3,0)),"""")"
    Range("A2").Select
   
    Sheets("Compliance").Visible = True
    Sheets("Compliance").Unprotect Password:=“password”
    Sheets("Compliance").Select
    'ActiveSheet.Unprotect
    'ActiveSheet.ShowAllData
    Worksheets("Compliance").AutoFilterMode = False
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("A2").Select
   
    Sheets("Title").Visible = True
    Sheets("Title").Unprotect Password:=“password”
    Sheets("Title").Select
    'ActiveSheet.Unprotect
    'ActiveSheet.ShowAllData
    Worksheets("Title").AutoFilterMode = False
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("A2").Select
   
    Sheets("Eviction").Visible = True
    Sheets("Eviction").Unprotect Password:=“password”
    Sheets("Eviction").Select
    'ActiveSheet.Unprotect
    'ActiveSheet.ShowAllData
    Worksheets("Eviction").AutoFilterMode = False
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("A2").Select
   
    Sheets("Additional Data").Visible = True
    Sheets("Additional Data").Unprotect Password:=“password”
    Sheets("Additional Data").Select
    'ActiveSheet.Unprotect
    'ActiveSheet.ShowAllData
    Worksheets("Additional Data").AutoFilterMode = False
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("A2").Select
   
    Sheets("Weekly Notes").Visible = True
    Sheets("Weekly Notes").Unprotect Password:=“password”
    Sheets("Weekly Notes").Select
    'ActiveSheet.Unprotect
    'ActiveSheet.ShowAllData
    Worksheets("Weekly Notes").AutoFilterMode = False
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("A2").Select
   
   
    Sheets("Closed").Visible = True
    Sheets("Closed").Select
    Sheets("Closed").Unprotect Password:=“password”
    'ActiveSheet.Unprotect
    'ActiveSheet.ShowAllData
    Worksheets("Closed").AutoFilterMode = False
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("A2").Select
   
End If
   
Errormessage: MsgBox "Macro aborted. Check Worksheet...has sheet been cleared already?"
 
End Sub

[patel]

Sub Clear_Worksheets()
If MsgBox("You are about to clear all data from each tab. Do you want to continue?", vbYesNo + vbExclamation) = vbNo Then Exit Sub
   for each Sh in Sheets
with Sh
if .name = "Inventory Status" then
.Visible = True
.Unprotect Password:=“password”
.Columns("A:AF").Hidden = False
.AutoFilterMode = False
    .Rows("2:2").Select
    .Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    .Columns("L").Hidden = True
    .Columns("P:R").Hidden = True
    .Columns("Y:AA").Hidden = True
    .Range("AC2").FormulaR1C1 = _
        "=IFERROR(IF(ISNA(VLOOKUP(RC[-26],Wkly_Notes,3,0)),VLOOKUP(RC[-26],Eviction,6,0),VLOOKUP(RC[-26],Wkly_Notes,3,0)),"""")"
    .Range("A2").Select
   else
    .Visible = True
    .Unprotect Password:=“password”
    .AutoFilterMode = False
    .Rows("2:2").Select
    .Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    .Range("A2").Select
   End If
 end with
   End Sub

[mrojas]

The way the On Error is used is as follows

On Error GoTo ErrorTrap

your code goes here

if an error is found, jump to the ErrorTrap block of code
if no errors are found, continue with next line of code
Continue executing lines of code
If all lines of code have been executed, exit sub

ExitPoint: ' This is the code block executed if there are no error or if directed to it

Exit Sub

ErrorTrap:
Some error message or other code to execute before exiting sub
GoTo ExitPoint ' Redirect execution to exit point

End Sub

[SamT]

Sub test()
With ThisWorkbook.Sheets
Rows("2:" & Rows.Count).Delete 'Or ClearContents
'
'
'
End With
End Sub

[shellecj]

Hello All, Sorry for the slow reply. I worked over at work yesterday and did not get home until late. Thank you so much for the suggestions above however I've been playing around with the different suggestions provided for the last couple hours but none seem to fix my problem.

For the first option provided above by Patel, the code is giving me an error stating For without Next. My code started with and if/end if but if rewriting the code using a For loop then where do I put the Next in my code?

For the clarification on the Error Trapping by mrojas, if I put my code in the ErrorTrap section, which in my case is an error msg, what then goes next to ExitPoint?

The last option provided by SamT does seem to work as well however I run into the same problem as my original code...if I go to run the macro and one of the sheets has already been cleared out (say manually) then again I get the runtime error. Error message I get is ""Run-time error '1004': Delete method of Range class failed

[mrojas]

You're missing your closing Next.
This should appear, from the bottom up, End With and End Sub
That should probably take care of the For without Next

All errors trapped should GoTo ExitPoint once you've dealt with as you see fit.

[mrojas]

Correction: This should appear, from the bottom up, between End With and End Sub

[shellecj]

No go...got an End With without With...sorry

[shellecj]

My code initially started with an if, then else, end if...but the first suggestion it was suggested to start with For Each but only the beginning of my original macro was revised...I just don't know how to modify the rest of it? The initial macro I posted did work but only if each of the worksheets (there are 7 total) still have data on them but if any of them happened to be cleared out, I get the run time error and the code aborts without finishing. What I need is the code to skip over the sheet and go on to the next if it happens to be cleared out already?

[ZVI]

Try this:

Sub Clear_Worksheets()
 
  Const PWD = "password"
  Dim Sh As Worksheet
 
  ' Turn blinking off
  Application.ScreenUpdating = False
 
  ' Trap errors
  On Error GoTo exit_
 
  ' Main
  For Each Sh In Worksheets
    With Sh
      ' Provide common actions for all sheets
      .Visible = True
      .Unprotect Password:=PWD
      .AutoFilterMode = False
      .UsedRange.Offset(1).EntireRow.Delete
      ' Activate/select the sheet before its cell selection
      .Activate
      .Range("A2").Select
      ' Format sheet "Inventory Status"
      If StrComp(.Name, "Inventory Status", vbTextCompare) = 0 Then
        .Columns("A:AF").Hidden = False
        .Columns("L").Hidden = True
        .Columns("P:R").Hidden = True
        .Columns("Y:AA").Hidden = True
        .Range("AC2").FormulaR1C1 = _
        "=IFERROR(IF(ISNA(VLOOKUP(RC[-26],Wkly_Notes,3,0)),VLOOKUP(RC[-26],Eviction,6,0),VLOOKUP(RC[-26],Wkly_Notes,3,0)),"""")"
      End If
    End With
    ' Shrink the used range
    With Sh.UsedRange: End With
  Next
 
  ' Activate by-default-sheet
  Worksheets("Inventory Status").Activate
 
exit_:
 
  ' Restore screen updating
  Application.ScreenUpdating = True
 
  ' For error show its details
  If Err Then MsgBox Err.Description, vbCritical, "Error #" & Err.Number

 
End Sub

[mrojas]

Can you post your new code?

[SamT]

Sub SamT()
Const PW As String = "password"

  If Not MsgBox("You are about to clear all data from each tab. Do you want to continue?", _
  vbYesNo + vbExclamation) Then Exit Sub
  'Error handling example
  On Error GoTo OOPS

 'With all sheets at once. If you have Charts, 'better use "With ThisWorkbook.Worksheets"
   With ThisWorkbook.Sheets
    Unprotect Password:=PW
    Columns.Visible = True
    Rows.Visible = True
    Rows("2:" & Rows.Count).ClearContents 'Or .Delete
  End With    '
       '
   With Sheets("Inventory Status")
    .Columns("L").Hidden = True
    .Columns("P:R").Hidden = True
    .Columns("Y:AA").Hidden = True
    .Range("AC2").FormulaR1C1 = _
    "=IFERROR(IF(ISNA(VLOOKUP(RC[-26],Wkly_Notes,3,0)),VLOOKUP(RC[-26]," _
    & "Eviction,6,0),VLOOKUP(RC[-26],Wkly_Notes,3,0)),"""")"
  End With
  
  'More code before Error handler here.
  
  'If no Error happens this Exit Sub will run.
Exit Sub
  
  'Code here will never run because it is after the Exit Sub
  'and before the GoTo Label.
  
OOPS:
  MsgBox "An Error occurred"
  
End Sub

[mrojas]

I think it's almost there. I would make one minor change to
End With

'More code before Error handler here.

ExitPoint:
'If no Error happens this Exit Sub will run.
Exit Sub

OOPS:
MsgBox "An Error occurred"

GoTo ExitPoint
End Sub


Any errors generated above the "End With" will be directed to the OOPS label. Below this label you execute whatever code you deem appropriate, maybe just a message box.
Then the next line (Goto ExitPoint) re-directs execution to the ExitPoint label. Execution continues with the following statement, the Exit Sub.

End of Story

[shellecj]

Hi All! Thank you so much for all the help.

ZVI...I did try your code but it deletes all worksheets which I don't want...just specific ones. SamT, I tried yours as well but results in an error (says sub or function not defined). And thank you mrojas, I will keep what you're saying in mind and try to go back and set it up in the sequence you're suggesting.

I'm going to go ahead and attach a workbook at this point. There's a button on the Inventory Status that says "Clear All Sheets" which is attached to the first macro I started with, "Clear_Worksheets" I've went through and scrubbed all sensitive data everywhere (I hope! LOL).


Again, I am very new to VBA , so bear with me.

Thanks again!

[SamT]

I use that construct when I am working with User Defined Objects: Dictionaries, Collections, Forms, Files, et al. Things that maybe won't be removed from memory on End Sub. Even then I usually use

End With 
     
     'More code before Error handler here.
     

'If no Error happens this GoTo will run.
Goto CleanExit
     
OOPS: 
    MsgBox "An Error occurred" 

     CleanExit:
'Code to clean up any left over bits from memory. 

End Sub

In this particular code, it is not needed.

IMHO.

YMMV.

[mrojas]

Cleaning up is a good habit to have.

[shellecj]

Cleaning up is a good habit to have.

yes indeed! ;-)

[ZVI]

ZVI...I did try your code but it deletes all worksheets which I don't want...just specific ones.

Well, this code works only in the specific sheets defined by constant MYSHEETS

Sub Clear_Worksheets_4()
 
  Const PWD = "password"
  Const MYSHEETS = "Inventory Status,Compliance,Title,Eviction,Additional Data,Weekly Notes,Closed Inventory"
  Dim x
 
  If MsgBox("WARNING! You are about to clear all data from each tab." & vbLf _
          & " Do you want to continue?", vbYesNo + vbExclamation) = vbNo Then Exit Sub
 
 
  ' Turn blinking off
  Application.ScreenUpdating = False
 
  ' Trap errors
  On Error GoTo exit_
 
  ' Main
  For Each x In Split(MYSHEETS, ",")
    With Sheets(Trim(x))
      ' Provide common actions for the sheet
      .Visible = True
      .Unprotect Password:=PWD
      .AutoFilterMode = False
      .UsedRange.Offset(1).EntireRow.Delete
       .Activate
      .Range("A2").Select
      ' Format the sheet "Inventory Status"
      If StrComp(.Name, "Inventory Status", vbTextCompare) = 0 Then
        .Columns("A:AF").Hidden = False
        .Columns("L").Hidden = True
        .Columns("P:R").Hidden = True
        .Columns("Y:AA").Hidden = True
        .Range("AC2").FormulaR1C1 = _
        "=IFERROR(IF(ISNA(VLOOKUP(RC[-26],Wkly_Notes,3,0)),VLOOKUP(RC[-26],Eviction,6,0),VLOOKUP(RC[-26],Wkly_Notes,3,0)),"""")"
      End If
    End With
    ' Shrink the used range
    With Sheets(x).UsedRange
    ' Do nothing - it's enough
    End With
  Next
 
  ' Activate by-default-sheet
  Worksheets("Inventory Status").Activate
 
exit_:
 
  ' Restore screen updating
  Application.ScreenUpdating = True
 
  ' For error show its details
  If Err Then MsgBox Err.Description, vbCritical, "Error #" & Err.Number
 
End Sub

[shellecj]

Hello ZVI,

For some reason the code is backing out on the Additional Data tab. Error given is Error #1004 Delete method of Range class failed

Also, just curious, but what does this part of the code do, For Each x In Split(MYSHEETS, ",")
With Sheets(Trim(x)) ? If you don't mind my asking? And is there supposed to be a variable type declared for x?

[ZVI]

Error can happen at trying of deleting the single row of the table.
To avoid such case replace the code line .UsedRange.Offset(1).EntireRow.Clear by .UsedRange.Offset(1).Clear

Taking into account the value of constant MYSHEETS the Split(MYSHEETS, ",") is equal to
Array("Inventory Status", "Compliance", "Title", "Eviction", "Additional Data", "Weekly Notes", "Closed Inventory").
See VBA help of Split function for more details.

The type of variable x is Variant.
If type of variable is not mentioned in Dim statement then it is Variant.
You can use Dim x As Variant as well as Dim x

Variable x in For Each x ... Next loop gets each value from the above mentioned Array.
Firstly x equals to "Inventory Status", then "Compliance" and so on.
And Sheets(Trim(x)) is the same as Sheets("Inventory Status") , then Sheets("Compliance") and so on