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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.