one summary sheet to convert into multiple files

[sanjay.goka]

Hi Seniors,

I have question, i have one consolidated sheet of all bank details and their pay. how can i convert that in to multiple sheets (like each bank one sheet it should create in the same sheet)

 

[Chihiro]

There are multiple methods (Advanced Filter, Pivot Table, etc). What's best suited for your file depends on your data structure. Can you upload sample workbook with enough data to mimic your set up?

 

[sanjay.goka]

Dear Senior
Thanks for responding, pls help as i need it urgently.
Eyee No. Employee Name Bank Name BankAccount Net Pay
00001 Mark ING VYSYA BANK LTD 324567986777 40000
00002 John ICICI BANK LTD 987654321098 50000
00003 Mathew HDFC BANK LTD. 98765432198765 20000
00004 Reddy ICICI BANK LTD 324567986777 10000
00005 Jackson ING VYSYA BANK LTD 324567986777 15600
00006 david ICICI BANK LTD 324567986777 15000
00007 manju HDFC BANK LTD. 98765432198765 20000
00008 parthu ICICI BANK LTD 324567986777 30000
this is main consolidate sheet in sheet1. my querry is there are three diff. banks are there, i need in sheet2 ING bank details, in sheet3 ICICI, in sheet4 HDFC like that with same headings bank wise multiple sheets should be created.
Thanks
sanjay

 

[Chihiro]

Here's sample using pivot. Just double click on Net Pay for each bank and it will create new sheet.

If this isn't what you are looking for, I'd need your expected output sheet layout.

 

[jindon]

It should be done via vba.

If you upload a workbook, it would help.

 

[sanjay.goka]

Hi, I have uploaded the out put file, i wanted this way only but every time i have to double click in home sheet. I request you that i wanted I think a program needs to run in vba or a program. if i click then automatically all sheets will get created at once. Please advice
Sanjay

 

[sanjay.goka]

Hi, greetings, if you give me sample program, i have large data and so many banks, that is why, if you also advise me how to do it would be much help. depending upon the file i shall include all banks. also give instructions.
thanks
sanjay

 

[Chihiro]

Use Ron de Bruin's code found in link.
http://www.rondebruin.nl/win/s3/win006_4.htm

Just modify FieldNum from 1 to 3.

 

[jindon]

Try

Sub test()
    Dim ws As Worksheet, a, i As Long, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    For Each ws In Worksheets
        If ws.Name <> "Sheet1" Then
            a = ws.Cells(1).CurrentRegion.Value
            For i = 1 To UBound(a, 1)
                If Not dic.exists(a(i, 3)) Then
                    dic(a(i, 3)) = a(i, 5)
                Else
                    If IsNumeric(a(i, 5)) Then dic(a(i, 3)) = dic(a(i, 3)) + a(i, 5)
                End If
            Next
        End If
    Next
    With Sheets("sheet1").Cells(1).Resize(dic.Count, 2)
        .CurrentRegion.ClearContents
        .Value = Application.Transpose(Array(dic.keys, dic.items))
        .Rows(dic.Count + 1) = Array("Grand Total", "=sum(r2c:r[-1]c")
    End With
End Sub