Copying records from sheet1 to sheet2??

[j2dizzo]

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

 

[Norman Jones]

Hi J2dizzo,

Try:

'==============>>
Private Sub Worksheet_Activate()
Dim SH As Worksheet
Const sStr As String = "School" '<==== CHANGE

Set SH = Me.Parent.Sheets("Sheet1") '<==== CHANGE

With SH

.AutoFilterMode = False

Application.EnableEvents = False

Me.UsedRange.ClearContents

.Range("A1").AutoFilter Field:=6, Criteria1:=sStr

.AutoFilter.Range.Copy
.Paste Destination:=Me.Range("A1")
Application.CutCopyMode = False
.Range("A1").AutoFilter

Application.EnableEvents = True

End With

End Sub
'<<==============

Change the sheet name and the search string (sStr) values to suit your
requirements.

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

*******************************************
Right-click the worksheet's tab

Select 'View Code' from the menu and paste the code.

Alt-F11 to return to Excel.
*******************************************

 

[Norman Jones]

Hi J2dizzo,

To add, in the line:

.Range("A1").AutoFilter Field:=6, Criteria1:=sStr

Change the Field value (6) to correspond to the requisite data column.

Assuming that the data on sheet1 starts in column A, then field 6 would
correspond to column F, as in your example.

 

[j2dizzo]

Thanks Norman for your reply.

It works perfectly.

 

[j2dizzo]

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

 

[j2dizzo]

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?

 

[Norman Jones]

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
'<<==============

 

[j2dizzo]

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.

 

[Norman Jones]

Hi J2dizzo,

See my reponse to your previous post.

 

[j2dizzo]

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