extract data from one sheett to different tabs in the same file.

[css123]

Hi All,

I want to extract the data from Main sheet based on the temperature (i.e. cool, warm or hot) in different tabs for each temp. status.

The data that will reflect in each temperature tabs should automatically populate in each sheet based on their temperatures and it would be similar to main sheet columns.



Could you pls help.

 

[My Aswer Is This]

Try this:
To install this code:

• Right-click on the “Master” Sheet tab
• Select View Code from the pop-up context menu
• Paste the below code in the VBA edit window
• You need to have a “Master Sheet” Name does not have to be Master
You need to have three sheets named:
“Cool” “Warm” “Hot”
Then when you enter either of these words in column “A” of the Master sheet that row of data will be copied over to the proper sheet.

All these words must be entered exactly.

If you want to change the column in the Master sheet the Change “A” in the script to which one you want.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("[COLOR="#FF0000"]A:A[/COLOR]")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long


    If Target.Value = "Cool" Or Target.Value = "Warm" Or Target.Value = "Hot" Then
Lastrow = Sheets(Target.Value).Cells(Rows.Count, "[COLOR="#FF0000"][/COLOR][COLOR="#FF0000"]A[/COLOR]").End(xlUp).Row + 1
        Sheets(Target.Value).Rows(Lastrow).Value = Rows(Target.Row).Value

    End If
End If
End Sub

 

[css123]

Try this:
To install this code:

• Right-click on the “Master” Sheet tab
• Select View Code from the pop-up context menu
• Paste the below code in the VBA edit window
• You need to have a “Master Sheet” Name does not have to be Master
You need to have three sheets named:
“Cool” “Warm” “Hot”
Then when you enter either of these words in column “A” of the Master sheet that row of data will be copied over to the proper sheet.

All these words must be entered exactly.

If you want to change the column in the Master sheet the Change “A” in the script to which one you want.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("[COLOR=#FF0000]A:A[/COLOR]")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long


    If Target.Value = "Cool" Or Target.Value = "Warm" Or Target.Value = "Hot" Then
Lastrow = Sheets(Target.Value).Cells(Rows.Count, "[COLOR=#FF0000]A[/COLOR]").End(xlUp).Row + 1
        Sheets(Target.Value).Rows(Lastrow).Value = Rows(Target.Row).Value

    End If
End If
End Sub

thats great. Thanks it worked. But when I try to make any changes it creates a duplicate row with same data under hot or cool tab. Anyway It can refresh the data in the same row?

 

[My Aswer Is This]

The only way that would work is if you always want the text on row one of Master to go row One of Cool sheet.
As it is written now it always goes to the first empty row of cool sheet.
But then if you put Hot on row 6 of master value would go on row 8 of Hot and so on.

 

[css123]

The only way that would work is if you always want the text on row one of Master to go row One of Cool sheet.
As it is written now it always goes to the first empty row of cool sheet.
But then if you put Hot on row 6 of master value would go on row 8 of Hot and so on.

Is there any ways if the data in Row 6 has status HOT so this will come under HOT tab but later has few additional columns like next meeting date etc.. and now I know the meeting date so I update it in the master sheet but it doesnt reflect in the Hot tab.

Appreciate your help.

 

[My Aswer Is This]

This is the only other way I know to write the script where you can make changes.
The row it's entered on in the Master is the row it will go on the cool warm or hot sheets

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long

    If Target.Value = "Cool" Or Target.Value = "Warm" Or Target.Value = "Hot" Then
        Sheets(Target.Value).Rows(Target.Row).Value = Rows(Target.Row).Value
    
    End If
End If
End Sub

 

[My Aswer Is This]

To have an event change activate you must change the value in column A. Even if you just reenter the same value. Changing a value in column B will not cause the event change to activate

 

[css123]

To have an event change activate you must change the value in column A. Even if you just reenter the same value. Changing a value in column B will not cause the event change to activate

Is adding below refresh command a good option to your existing code so it can refresh the sheet every 5 seconds.?

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Private Sub Workbook_Open()
alertTime = Now + TimeValue("00:00:05") 'hh:mm:ss
Application.OnTime alertTime, "Refresh"
End Sub</code>