Get Macro to Work on Multiple Sheets

[Chris_010101]

Hello,

The below macro is assigned to a button on the ribbon and works on the "current 6 months" sheet. It looks down column B for "terminated" and deletes the row. It's currently restricted to only work on "current 6 months" sheet.

Is there any way to add another sheet "Previous 6 months" to it, so it will work on both sheets only?

Sub deleteTerminatedEmployees()                     'v1b

Range("A1").Select
    Selection.End(xlUp).Select
    Range("A1").Select

'****************************************
'CHECK FOR APPROPRIATE WORKSHEET..
'****************************************
zSheet = "Current 6 Months"                         '<< specify required sheet tabname


If ActiveSheet.Name <> zSheet Then                  'on wrong sheet!
saywhat = "This routine only works on sheet tabs [Current 6 Months]."   'message txt
saywhat = saywhat & vbCr & vbCr
saywhat = saywhat & "Switch to correct sheet and try again!"
boxtitle = "Employment Status"                      'message box heading
btns = vbOKOnly + vbExclamation                     'message box buttons
answer = MsgBox(saywhat, btns, boxtitle)            'display message box
Exit Sub                                            'nothing else to do
End If                                              'end of test for a valid worksheet
'****************************************

'Worksheet is appropriate, so continue here..
Set zBlock = [a1].CurrentRegion                     'set shortcut for data block

'set filter for column B..                          'for Field, col A = 1, col B = 2 etc
zBlock.AutoFilter Field:=2, Criteria1:="Terminated" 'set required filter for column 2
   
zCount = zBlock.Columns(1). _
   SpecialCells(xlCellTypeVisible).Count - 1        'filtered records; -1=ignore header row
   
If zCount = 0 Then                                  'no matches for this filter
saywhat = "There are NO records in the filtered block for status Terminated!"   'message txt
boxtitle = "Employment Status"                      'message box heading
btns = vbOKOnly + vbExclamation                     'message box buttons
answer = MsgBox(saywhat, btns, boxtitle)            'display message box

zBlock.AutoFilter Field:=2                          'cancel the 'Terminated' filter
Exit Sub                                            'nothing else to do
End If                                              'end of test for NO status Terminated

'There are filtered records for status Terminated, so request confirmation before deleting..
If zCount = 1 Then
saywhat = "There is ONE record in the filtered block for status Terminated!"   'message txt
Else                                                                            'otherwise..
saywhat = "There are " & zCount & " records in the filtered block for status Terminated!"
End If                                              'end of check for number of records

saywhat = saywhat & vbCr & vbCr                     'add 2 lines to message text
saywhat = saywhat & "Do you want to delete these?"  'add text

btns = vbYesNoCancel + vbDefaultButton2             'message box buttons; set default to btn2

answer = MsgBox(saywhat, btns, boxtitle)            'display message box

If answer <> vbYes Then                             'User did NOT click [Yes] button, so..
zBlock.AutoFilter Field:=2                          'cancel the 'Terminated' filter
Exit Sub                                            'nothing else to do
End If

'User has confirmed [Yes] to delete the filtered records, so..

zBlock.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

zBlock.AutoFilter Field:=2                          'cancel the 'Terminated' filter

End Sub

Thanks in advance

 

[PeteWright]

Hi!

When running the code I get an error since I don't have any corresponding data in my sheet, but you could try the following

Replace this code:

zSheet = "Current 6 Months"                         '<< specify required sheet tabname


If ActiveSheet.Name <> zSheet Then                  'on wrong sheet!

with this code:

zSheetCurr = "Current 6 Months"
zSheetPrev = "Previous 6 Months"
If ActiveSheet.Name <> zSheetCurr And ActiveSheet.Name <> zSheetPrev Then