Dim as WSS, set multiple sheets as variables, give separate command for each sheet

patsfan1

New Member
Hello,
I am attempting to put together code that will clear a range of cells from one worksheet, and then a separate range from another worksheet afterward. I'm getting a compile error, "wrong number of arguments/invalid property assign". I am not exactly clear on how to set the worksheets as variables and then assign the commands to clear the ranges. Any help is appreciated. Thanks

Code:
Private Sub CommandButton2_Click()
 
Application.ScreenUpdating = False
 
      Dim Wss As Worksheet
      
      Sheets("Sheet1").Visible = True
      Sheets("Rent Roll").Visible = True
      
      Set Wss = Sheets("Sheet1", "Rent Roll")
      With ws("Sheet1")
      
      Range("a2:q31").<wbr>ClearContents   'Clear Sheet 1 Tab
      
      With ws("Rent Roll")
      
      'Clear Rent Roll Tab
         
      Range("D6:H35").<wbr>ClearContents
      Range("J6:J35").ClearContents
      Range("M6:M35").ClearContents
      Range("R6:R35").ClearContents
      Range("T6:U35").ClearContents
      Range("x6:y35").ClearContents
      Range("aa6:ab35").<wbr>ClearContents
      Range("ae6:ag35").<wbr>ClearContents
      
      End With
    End with
    
        Wss.Visible = xlSheetVeryHidden
    
    Application.ScreenUpdating = True
    
End Sub
 

Fluff

MrExcel MVP, Moderator
How about
Code:
Private Sub CommandButton2_Click()
 
Application.ScreenUpdating = False
 
      With Sheets("Sheet1")
         .Visible = True
         .Range("a2:q31").ClearContents   'Clear Sheet 1 Tab
         .Visible = xlSheetVeryHidden
      End With
      With Sheets("Rent Roll")
          .Visible = True
         'Clear Rent Roll Tab
         .Range("D6:H35").ClearContents
         .Range("J6:J35").ClearContents
         .Range("M6:M35").ClearContents
         .Range("R6:R35").ClearContents
         .Range("T6:U35").ClearContents
         .Range("x6:y35").ClearContents
         .Range("aa6:ab35").ClearContents
         .Range("ae6:ag35").ClearContents
         .Visible = xlSheetVeryHidden
      End With
End Sub
 

Some videos you may like

This Week's Hot Topics

Top