VBA to autofilter in a specific column for each criteria and copy the result to a new sheet

Need to filter on each region (Column F) and copy the result to a new worksheet. would be helpful if this can be done through looping as the original data set contains more regions. Thanks for the help
https://drive.google.com/file/d/0B02...5nbDlvME0/view

thanks
kishore naiduAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Your file link is broken. Why didn't you upload the file here itself?

Anyways please find attached a sample workbook with some dummy data along with a button on Sheet1 and code on Module1.
Please click the button to run the code. The code will copy the data for all the regions to their respective sheets.
Sub FilterByRegionAndCopyData()
Dim sws As Worksheet, dws As Worksheet
Dim dict, it, x
Dim lr As Long, i As Long
Application.ScreenUpdating = False
Set sws = Sheets("Sheet1")  'Sheet which contains data for all the regions
lr = sws.Cells(Rows.Count, 6).End(xlUp).Row
x = sws.Range("F2:F" & lr).Value
Set dict = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(x, 1)
   dict.Item(x(i, 1)) = ""
Next i
For Each it In dict.keys
   With sws.Range("A1").CurrentRegion
      .AutoFilter field:=6, Criteria1:=it
      On Error Resume Next
      Set dws = Sheets(it)
      dws.Cells.Clear
      On Error GoTo 0
      If dws Is Nothing Then
         Sheets.Add(after:=Sheets(Sheets.Count)).Name = it
         Set dws = ActiveSheet
      End If
      sws.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy dws.Range("A1")
      .AutoFilter
   End With
   Set dws = Nothing
Next it
Application.ScreenUpdating = False
MsgBox "Data for all the regions has been copied to their respective region sheet successfully.", vbInformation, "Done!"
End Sub

Open in new window

FilterAndCopyData.xlsm
0
 
kishore naiduAuthor Commented:
Thanks a lot. This is more than what i expected.i shall change the code to suit to the actual data.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Kishore! Glad to help.
0
All Courses

From novice to tech pro — start learning today.