Copying records from sheet1 to sheet2??

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

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.
*******************************************

---
Regards,
Norman


"j2dizzo" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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.

---
Regards,
Norman


"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> 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.
> *******************************************
>
> ---
> Regards,
> Norman
>
>
> "j2dizzo" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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.

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

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.

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

Hi J2dizzo,

See my reponse to your previous post.


---
Regards,
Norman



"j2dizzo" <[email protected]> wrote in
message news:[email protected]...
>
> 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.