Possible zinger, can it be done without VB?

[Eltis]

Consider I have a table of data on sheet 1, grouped by a code in the first column.

What I want is that if the code is A, copy the contents of this line to sheet 2. If the code is B, copy the line to sheet 3, etc. I also need to sum the contents of various lines on each sheet.

I have thought about just copying the entire contents to each sheet and applying a filter, but that causes more work and also creates problems with summing. Any ideas?

[Juan Pablo González]

Ok, my usual question... why without VBA ? I mean, there's a limit to what you can do efficiently with formulas !

[Eltis]

Hi, no reason really except I'm not very familiar with VB so I was just wondering if I could work around it. If there is no other way, I would welcome your suggestions using VB and go from there. Thanks.

[Yogi Anand]

On 2002-05-22 16:32, Eltis wrote:
Consider I have a table of data on sheet 1, grouped by a code in the first column.

What I want is that if the code is A, copy the contents of this line to sheet 2. If the code is B, copy the line to sheet 3, etc. I also need to sum the contents of various lines on each sheet.

I have thought about just copying the entire contents to each sheet and applying a filter, but that causes more work and also creates problems with summing. Any ideas?

As Juan said, VBA is a better choice in this case, however, a non-VBA approach would be ...
If your requirements are very specific as you have stated in here, and there are not too many cells involved, you could put formulas in affected cells in sheets 2 and 3 to suit your needs, e.g., let us take one cell A1

in sheet2, cell A1, =if(sheet1!A1_code="A",value_of_A1,"")

etc.,

Regards!

[Nimrod]

This should do the trick for you..
Note that there are 3 places to change the names of sheets. IE. Sheet1 should be changed to the name of the original sheet and Sheet2 and Sheet3 changed to the names of your target sheets.

Sub CopyOneSheetsToMany()
With Worksheets("Sheet1") 'Source Sheet
For Each c In .Range("A1:A" & .Cells(65536, 1).End(xlUp).Row)
        If UCase(Trim(c.Value)) = "A" Then TargetSheet = "Sheet2" 'Targ sheetA
        If UCase(Trim(c.Value)) = "B" Then TargetSheet = "Sheet3" 'Targ sheetB
        TgRw = Sheets(TargetSheet).Cells(65536, 1).End(xlUp).Row + 1
        .Range(c.Row & ":" & c.Row).Copy Destination:=Worksheets(TargetSheet) _
        .Range(TgRw & ":" & TgRw)
    Next c
 End With
End Sub

[Eltis]

Thank you all, very helpful!