Sub Egypt_EG()
'Egypt_EG Macro
Dim wb1 As Workbook, wb2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim tb1 As ListObject, tb2 As ListObject
Dim arr As Variant, i As Long
'Settings
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb1 = ThisWorkbook
Set sh1 = wb1.Sheets("Combined_User_Stories")
Set tb1 = sh1.ListObjects("Table1")
arr = Array("Country_Filters", "Instructions", "Combined_User_Stories", "Issues_Log", _
"CORE_IC_Notes", "Self_Service_Request_Types", "Pay_Codes", "Holiday_Calendar_Table", _
"Accrual_Codes", "EeT_Inbound_Load", "WFMgr_Inbound_Load", "GWFM_Outbound")
'Unhidding all tabs
Call hide_unhide_tabs(wb1, arr, True)
'Selecting the correct country
With wb1.SlicerCaches("Slicer_Egypt_EG")
.SlicerItems("X").Selected = True
.SlicerItems("(blank)").Selected = False
End With
'Copying the tabs to a new workbook
For i = 1 To UBound(arr)
If i = 1 Then
wb1.Sheets(arr(i)).Copy
Set wb2 = ActiveWorkbook
Else
wb1.Sheets(arr(i)).Copy After:=wb2.Sheets(wb2.Sheets.Count)
End If
Next
'Clear All Filters
Set sh2 = wb2.Sheets("Combined_User_Stories")
Set tb2 = sh2.ListObjects("Table1")
tb2.AutoFilter.ShowAllData
If sh2.FilterMode = True Then sh2.ShowAllData
With tb2.DataBodyRange
If .Rows.Count > 1 Then
.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
End If
.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
tb1.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy .Cells(1)
End With
'Selecting the other countries and deleting them
sh2.Range("AC:AL").Delete Shift:=xlToLeft
Range("AD:CZ").Delete Shift:=xlToLeft
'Hide "Place Holder" Columns
sh2.Range("M:N,V:W,AA:AB").EntireColumn.Hidden = True
'Saving country specific workbook on temp folder
wb2.Sheets(1).Select
wb2.SaveAs ("C:\temp\Egypt_EG.xlsx")
wb2.Close SaveChanges:=False
'Diselecting Country
wb1.Activate
wb1.SlicerCaches("Slicer_Egypt_EG").ClearManualFilter
Sheets("Instructions_Main").Select
'Re-Hidding Tabs
Call hide_unhide_tabs(wb1, arr, False)
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Sub hide_unhide_tabs(wb1 As Workbook, arr As Variant, bln As Boolean)
Dim ar As Variant
For Each ar In arr
If ar <> "Combined_User_Stories" Then wb1.Sheets(ar).Visible = bln
Next
End Sub
