Excel Split information accross sheets

[Amran_Ali]

Hi Guys,

I wanted information to be split acroos a few sheets.

I have four sheets "Main", "London", "manchester" and "liverpool".

[TABLE="width: 500"]
<tbody>[TR]
[TD]Advisor[/TD]
[TD]Date[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In the "main" sheet the location column has drop down list of different locations e.g. London, Manchester and liverpool

Every time an advisor fills the form and a location in selected i wanted that row of information to go directly to that sheet.

I hope that makes sense, please help...


Thank You

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in the drop down list. I am assuming that your drop down list is in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Target.EntireRow.Copy Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Application.ScreenUpdating = True
End Sub

 

[Amran_Ali]

sorry maybe if would be clearer if i explained it like this

On Sheet1 If cell I4 contains "London" cells B4 to H4 to copy to Sheet 2.

I dont know if that explains it any better

 

[Smitty]

Welcome to the Board!

It's generally not a good idea to split up your data, as you lose the ability to easily analyze it. Instead you can create a master pivot table with Location in the Filter field. Then goto Pivot Table Options-->Show Report Filter Pages and Excel will create a new pivot table worksheet for each item in the list. Now all you need to do is Refresh the pivot tables whenever you add data to update them.

HTH,

 

[mumps]

I interpreted your explanation in your original post to mean that you wanted to copy the data to the sheet with the same name as the location from the drop down list. If that is the case, then try this macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Range("B" & Target.Row & ":H" & Target.Row).Copy Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Application.ScreenUpdating = True
End Sub

In your last post you say that you want to copy it to Sheet2. In that case use this macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Range("B" & Target.Row & ":H" & Target.Row).Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Application.ScreenUpdating = True
End Sub