Hi
I have a excel file with three datas emp id , emp name and location. I separate the data based on the location filter
I am using the below macro code. but when i make the heading as merge the below macro is not working. It filtered only the blank cells.
Attaching the file for your reference. Kindly help
>>> use code - tags <<<
I have a excel file with three datas emp id , emp name and location. I separate the data based on the location filter
I am using the below macro code. but when i make the heading as merge the below macro is not working. It filtered only the blank cells.
Attaching the file for your reference. Kindly help
>>> use code - tags <<<
Code:
Sub filtersave()
Dim noDupes As New Collection
Dim rw As Long
Dim itm As Variant
Selection.AutoFilter Field:=3
rw = ActiveSheet.AutoFilter.Range.Row
For Each cell In ActiveSheet.AutoFilter.Range.Columns(3).Cells
If cell.Row <> rw Then
On Error Resume Next
noDupes.Add cell.Value, cell.Text
On Error GoTo 0
End If
Next
For Each itm In noDupes
Selection.AutoFilter Field:=3, Criteria1:=itm
Cells.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & itm & ".xls", FileFormat:=xlWorkbookNormal
ActiveWorkbook.Close (1)
Next
End Sub
Attachments
Last edited by a moderator: