Sort and Copying
 

I have a column in my spreadsheet with only two types of information, I need
to separate them into two new sheets with its respective rows.

Sort and Copying
 

Hi Abilio.

Consider using the Advanced Filter feature.

If you need to automate this, turn on the macro recorder while you perform
the operations manually. This will provide you with code which can be edited
to provide generic application.

If you have any problems in editing the code, post back with details.

If you are not familiar with the Advanced filter feature, see Debra
Dalgeish's tutorial at:

http://www.contextures.com/xladvfilter01.html

Note particularly, the section in Debra's tutorial entitled: 'Extract Data
to Another Worksheet'.


---
Regards,
Norman



"Abilio" wrote in message
...
I have a column in my spreadsheet with only two types of information, I
need
to separate them into two new sheets with its respective rows.

Sort and Copying
 

Thank you very much for the tip Norman!
I have the code, now I need to tell the macro that the range varies
according to the two types of info I have on column C. It is kind of:
From master sheet
Copy to sheet 1 all the rows on column C that contains "household"
Copy to sheet 2 all the cells on column C that contains "Persons 2-99"

I appreciate your help very much,

Abilio Andries

"Norman Jones" wrote:

Hi Abilio.

Consider using the Advanced Filter feature.

If you need to automate this, turn on the macro recorder while you perform
the operations manually. This will provide you with code which can be edited
to provide generic application.

If you have any problems in editing the code, post back with details.

If you are not familiar with the Advanced filter feature, see Debra
Dalgeish's tutorial at:

http://www.contextures.com/xladvfilter01.html

Note particularly, the section in Debra's tutorial entitled: 'Extract Data
to Another Worksheet'.


---
Regards,
Norman



"Abilio" wrote in message
...
I have a column in my spreadsheet with only two types of information, I
need
to separate them into two new sheets with its respective rows.

Sort and Copying
 

Hi Abilio,

Try something like:

'==============
Public Sub Worksheet_Activate()
Dim Sh As Worksheet
Dim SH1 As Worksheet
Dim SH2 As Worksheet
Dim tSH As Worksheet
Dim arr1 As Variant
Dim arr2 As Variant
Dim i As Long
Const sStr1 As String = "household" '<<==== CHANGE
Const sStr2 As String = "Persons 2-99" '<<==== CHANGE

arr1 = Array("household", "Persons 2-99")
arr2 = Array("Sheet1", "Sheet2") '<<==== CHANGE

Set Sh = ThisWorkbook.Sheets("MASTER") '<<==== CHANGE

For i = LBound(arr1) To UBound(arr1)
With Sh
.Parent.Sheets(arr2(i)).UsedRange.ClearContents
.AutoFilterMode = False
.Range("A1").AutoFilter Field:=3, Criteria1:=arr1(i)
.AutoFilter.Range.Copy
.Paste Destination:= _
Sh.Parent.Sheets(arr2(i)).Range("A1")
Application.CutCopyMode = False
.Range("A1").AutoFilter

End With
Next i

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

---
Regards,
Norman


"Abilio" wrote in message
...
Thank you very much for the tip Norman!
I have the code, now I need to tell the macro that the range varies
according to the two types of info I have on column C. It is kind of:
From master sheet
Copy to sheet 1 all the rows on column C that contains "household"
Copy to sheet 2 all the cells on column C that contains "Persons 2-99"

I appreciate your help very much,

Abilio Andries

"Norman Jones" wrote:

Hi Abilio.

Consider using the Advanced Filter feature.

If you need to automate this, turn on the macro recorder while you
perform
the operations manually. This will provide you with code which can be
edited
to provide generic application.

If you have any problems in editing the code, post back with details.

If you are not familiar with the Advanced filter feature, see Debra
Dalgeish's tutorial at:

http://www.contextures.com/xladvfilter01.html

Note particularly, the section in Debra's tutorial entitled: 'Extract
Data
to Another Worksheet'.


---
Regards,
Norman



"Abilio" wrote in message
...
I have a column in my spreadsheet with only two types of information, I
need
to separate them into two new sheets with its respective rows.

Sort and Copying
 

Hi Abilio,

Sorry!

Public Sub Worksheet_Activate()

Should have read:

Public Sub Tester

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Abilio,

Try something like:

'==============
Public Sub Worksheet_Activate()
Dim Sh As Worksheet
Dim SH1 As Worksheet
Dim SH2 As Worksheet
Dim tSH As Worksheet
Dim arr1 As Variant
Dim arr2 As Variant
Dim i As Long
Const sStr1 As String = "household" '<<==== CHANGE
Const sStr2 As String = "Persons 2-99" '<<==== CHANGE

arr1 = Array("household", "Persons 2-99")
arr2 = Array("Sheet1", "Sheet2") '<<==== CHANGE

Set Sh = ThisWorkbook.Sheets("MASTER") '<<==== CHANGE

For i = LBound(arr1) To UBound(arr1)
With Sh
.Parent.Sheets(arr2(i)).UsedRange.ClearContents
.AutoFilterMode = False
.Range("A1").AutoFilter Field:=3, Criteria1:=arr1(i)
.AutoFilter.Range.Copy
.Paste Destination:= _
Sh.Parent.Sheets(arr2(i)).Range("A1")
Application.CutCopyMode = False
.Range("A1").AutoFilter

End With
Next i

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

---
Regards,
Norman

Sort and Copying
 

I love you Norman

Thank you,

Abilio Andries

"Norman Jones" wrote:

Hi Abilio,

Sorry!

Public Sub Worksheet_Activate()

Should have read:

Public Sub Tester

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Abilio,

Try something like:

'==============
Public Sub Worksheet_Activate()
Dim Sh As Worksheet
Dim SH1 As Worksheet
Dim SH2 As Worksheet
Dim tSH As Worksheet
Dim arr1 As Variant
Dim arr2 As Variant
Dim i As Long
Const sStr1 As String = "household" '<<==== CHANGE
Const sStr2 As String = "Persons 2-99" '<<==== CHANGE

arr1 = Array("household", "Persons 2-99")
arr2 = Array("Sheet1", "Sheet2") '<<==== CHANGE

Set Sh = ThisWorkbook.Sheets("MASTER") '<<==== CHANGE

For i = LBound(arr1) To UBound(arr1)
With Sh
.Parent.Sheets(arr2(i)).UsedRange.ClearContents
.AutoFilterMode = False
.Range("A1").AutoFilter Field:=3, Criteria1:=arr1(i)
.AutoFilter.Range.Copy
.Paste Destination:= _
Sh.Parent.Sheets(arr2(i)).Range("A1")
Application.CutCopyMode = False
.Range("A1").AutoFilter

End With
Next i

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

---
Regards,
Norman