Problem 1 :- Filter Data and paste to other sheet

Dear Sir / madam,

Please see the attachment.  

I have a problem I want to copy data base on the Person name which i highlighted with yellow colour.
suppose if the person name "JKM" a sheet will created with the name "JKM" and the entire rows data relating to Jkm WILL copy to the new sheets of JKM.  if the person name is HM the the same rule will be follow.




Attachment

https://drive.google.com/file/d/0B2BJddV6HsaWUkxicUp0TlVxb1U/view?usp=sharing



Sub Test()
Dim J As Integer
Dim sh As Worksheet
Sheet1.Range("M:M").Copy Sheet1.Range("Z:Z")
Sheet1.Range("Z:Z").RemoveDuplicates Columns:=1, Header:=xlNo
'Set sh = Worksheets
For J = Sheet1.Range("Z" & Rows.Count).End(xlUp).Row To 3 Step -1

    ActiveSheet.Range("A2:M" & Sheet1.Range("M" & Rows.Count).End(xlUp).Row).AutoFilter Field:=13, Criteria1:=Sheet1.Cells(J, 26)


Sheet1.Range("A2:M" & Sheet1.Range("M" & Rows.Count).End(xlUp).Row).Select
Selection.SpecialCells(xlCellTypeVisible).Copy
Set sh = Worksheets.Add
sh.Range("A1").PasteSpecial xlPasteAll
sh.Name = Sheet1.Cells(J, 26)
Sheet1.Activate
ActiveSheet.Range("A2:M" & Sheet1.Range("M" & Rows.Count).End(xlUp).Row).AutoFilter
Next J
Sheet1.Range("Z:Z").Delete

MsgBox "Thanks"

End Sub

Comments

Post a Comment

jeetexceltips@gmail.com

Popular posts from this blog

50 Excel VBA Oral Interview Questions

Ques 01. What is the difference between ByVal and ByRef and which is default ? Solution: ByRef: If you pass an argument by reference when calling a procedure the procedure access to the actual variable in memory. As a result the variable's value can be changed by the procedure. ByVal: If you pass an argument by value when calling a procedure the variable's value can be changed with in the procedure only outside the actual value of the variable is retained. ByRef is default: Passing by reference is the default in VBA. If you do not explicitly specify to pass an argument by value VBA will pass it by reference. Ques 02. What is the meaning of Option Explicit and Option Base? Solution: Option Explicit makes the declaration of Variables Mandatory while Option Base used at module level to declare the default lower bound for array subscripts. For eg. Option Base 1 will make the array lower bound as 1 instead of 0. Option Base - When an array is declared by d
Read more