• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

One sheet into multiple sheets based on criteria


I want to copy the data into different sheets based on the name as criteria

I have pasted the data of one customer i want the data to paste the data of all customers

for one customer one sheet



Well-Known Member
See button in attached, which runs the following:
Sub blah()
'Application.ScreenUpdating = False 'include this line and the last line of this macro to speed it up
Set RngToFilter = Sheets("Sheet1").Range("A1").CurrentRegion.Resize(, 11)
RngToFilter.Columns(10).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Sheet1").Range("Q1"), Unique:=True
Set AllCritRng = Sheets("Sheet1").Range("Q1").CurrentRegion
Set RngCrit = AllCritRng.Resize(2, 1)
For Each cll In Intersect(AllCritRng, AllCritRng.Offset(1)).Cells
  RngCrit.Cells(2).Value = cll.Value
  With Sheets.Add(After:=Sheets(Sheets.Count))
    RngToFilter.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=RngCrit, CopyToRange:=.Range("A1"), Unique:=False
    .Name = Left(cll.Value, 31)
  End With
Next cll
'Application.ScreenUpdating = True 'include this line and the first line of this macro to speed it up
End Sub