Hi all,

How can a create a sheet2 that will be populated by copied data from
sheet1? Sheet1 will contain all data and new records will be entered
manually into this sheet. Whenever sheet2 is opened, it's populated by
copying each row in sheet1 that its cell F (for example) matches a
pattern.

Pseudocode:

select a row
while true
set cell F as activecell
if text in activecell == "School"
then
copy current row's record to clipboard
append record in clipboard to sheet2
go to next next row in sheet1
else
go to next row in sheet1
endif
repeat loop


Any help will be appreciated. Thanks in advance

~j2dizzo


--
j2dizzo
------------------------------------------------------------------------
j2dizzo's Profile: http://www.excelforum.com/member.php...o&userid=22016
View this thread: http://www.excelforum.com/showthread...hreadid=489770

Thanks Norman for your reply.

It works perfectly.


--
j2dizzo
------------------------------------------------------------------------
j2dizzo's Profile: http://www.excelforum.com/member.php...o&userid=22016
View this thread: http://www.excelforum.com/showthread...hreadid=489770

Hi,

One problem with the code provided by Norman is that the table heade
in sheet2 is lost when the code runs.

Another problem is that the sequential number from the parent shee
remains the same when pasted in sheet2. To elaborate more, the column
of the parent sheet are sequential numbers of the rows. So assuming
search pattern is matched at row 7, the row is copied to the chil
sheet with 7 in column A instead of numbering the rows all over.

Anyone know how I can resolve this? Thank

--
j2dizz
-----------------------------------------------------------------------
j2dizzo's Profile: http://www.excelforum.com/member.php...fo&userid=2201
View this thread: http://www.excelforum.com/showthread.php?threadid=48977

I know that the line

Me.UsedRange.ClearContents

clears the cells with data in them but how do I omit clearing the
headers which are on Row 1 through Row 4?


--
j2dizzo
------------------------------------------------------------------------
j2dizzo's Profile: http://www.excelforum.com/member.php...o&userid=22016
View this thread: http://www.excelforum.com/showthread...hreadid=489770

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

I can't get the code below to work with the code provided by Norman

'Clears a range of cells in Column A in sheet2
Me.Range("A16:A9999").ClearContents

'AutoFills column A in sheet2 using a range from the parent sheet
..Range("A16:A17").AutoFill Destination:=Me.Range("A16:A114")

Any help will be greatly appreciated.


--
j2dizzo
------------------------------------------------------------------------
j2dizzo's Profile: http://www.excelforum.com/member.php...o&userid=22016
View this thread: http://www.excelforum.com/showthread...hreadid=489770

Hi J2dizzo,

See my reponse to your previous post.


---
Regards,
Norman



"j2dizzo" wrote in
message ...

I can't get the code below to work with the code provided by Norman

'Clears a range of cells in Column A in sheet2
Me.Range("A16:A9999").ClearContents

'AutoFills column A in sheet2 using a range from the parent sheet
Range("A16:A17").AutoFill Destination:=Me.Range("A16:A114")

Any help will be greatly appreciated.


--
j2dizzo
------------------------------------------------------------------------
j2dizzo's Profile:
http://www.excelforum.com/member.php...o&userid=22016
View this thread: http://www.excelforum.com/showthread...hreadid=489770

Thanks again Norman. I now have a fully working worksheets.


--
j2dizzo
------------------------------------------------------------------------
j2dizzo's Profile: http://www.excelforum.com/member.php...o&userid=22016
View this thread: http://www.excelforum.com/showthread...hreadid=489770