Hi J2dizzo,
One problem with the code provided by Norman is that the table header
in sheet2 is lost when the code runs.
Another problem is that the sequential number from the parent sheet
remains the same when pasted in sheet2. To elaborate more, the column A
of the parent sheet are sequential numbers of the rows. So assuming a
search pattern is matched at row 7, the row is copied to the child
sheet with 7 in column A instead of numbering the rows all over.
Possibly, these represent problems because neither issue was discussed in
your question: no mention was made of four header rows or of the need to
insert (new) sequential nummbering in column A.
To address your additional requirements, try the folowing version:
'==============
Private Sub Worksheet_Activate()
Dim Sh As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim LCell As Range
Const sStr As String = "School" '<==== CHANGE
Set Sh = Me.Parent.Sheets("Sheet1")
With Sh
.AutoFilterMode = False
Set rng = Me.UsedRange
Set rng = rng.Offset(4)
On Error Resume Next
Set rng = rng.Resize(rng.Rows.Count - 4)
On Error GoTo 0
rng.ClearContents
On Error GoTo XIT
Application.EnableEvents = False
.Range("A1").AutoFilter Field:=6, Criteria1:=sStr
.AutoFilter.Range.Copy
.Paste Destination:=Me.Range("A5")
Application.CutCopyMode = False
.Range("A1").AutoFilter
Set LCell = Cells(Rows.Count, "A").End(xlUp)
Set rng2 = Range("A5", LCell)
Range("A5").Value = 1
Range("A5").AutoFill Destination:=rng2, _
Type:=xlFillSeries
End With
XIT:
Application.EnableEvents = True
End Sub
'<<==============
---
Regards,
Norman
"j2dizzo" wrote in
message ...
Hi,
One problem with the code provided by Norman is that the table header
in sheet2 is lost when the code runs.
Another problem is that the sequential number from the parent sheet
remains the same when pasted in sheet2. To elaborate more, the column A
of the parent sheet are sequential numbers of the rows. So assuming a
search pattern is matched at row 7, the row is copied to the child
sheet with 7 in column A instead of numbering the rows all over.
Anyone know how I can resolve this? Thanks
--
j2dizzo
------------------------------------------------------------------------
j2dizzo's Profile:
http://www.excelforum.com/member.php...o&userid=22016
View this thread: http://www.excelforum.com/showthread...hreadid=489770