Get Macro to Work on Multiple Sheets

Rob_010101

Board Regular
Joined
Jul 24, 2017
Messages
198
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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:
VBA Code:
zSheet = "Current 6 Months"                         '<< specify required sheet tabname


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

with this code:
VBA Code:
zSheetCurr = "Current 6 Months"
zSheetPrev = "Previous 6 Months"
If ActiveSheet.Name <> zSheetCurr And ActiveSheet.Name <> zSheetPrev Then
 
Upvote 0

Forum statistics

Threads
1,223,157
Messages
6,170,420
Members
452,325
Latest member
BlahQz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top