vendor name wise split on diffrent sheet with total of each vendor

[Shabbo]

dear sir,

i want attached sheet to be splited vendor wise in diffent tab and total of each vendor at the bottom ffor
billing, adjusted, debits, credits outstanding.
pls help.

 

[Nebu]

Hi:

This is a commonly asked question in this forum. Use the search box provided on the top right hand corner of the screen and search for "split worksheet into multiple sheets", you will get the VBA code you are looking for.

Thanks

 

[Shabbo]

Hi, i searched and found as well but formates are diffrent and i am not able to edit that code as per my data.
Pls help

 

[Nebu]

Hi:

Try the following code

Sub SplitSheets()
Application.ScreenUpdating = False
Dim i, j, l As Long
Sheet1.ShowAllData
i = Sheet1.Cells(Rows.Count, "S").End(xlUp).Row
For j = 2 To i
With Sheet1
    .Range("A1:O1").AutoFilter field:=4, Criteria1:=.Range("S" & j)
    Sheets.Add after:=Sheet1
    ActiveSheet.Name = .Range("S" & j)
    .Range("A1:O" & Sheet1.Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets(.Range("S" & j).Text).Range("A1")
    Application.CutCopyMode = False
    l = Sheets(.Range("S" & j).Text).Cells(Rows.Count, "A").End(xlUp).Row
    Sheets(.Range("S" & j).Text).Range("K" & l + 1) = Application.WorksheetFunction.Sum(Sheets(.Range("S" & j).Text).Range("K2:K" & l))
    Sheets(.Range("S" & j).Text).Range("L" & l + 1) = Application.WorksheetFunction.Sum(Sheets(.Range("S" & j).Text).Range("L2:L" & l))
    Sheets(.Range("S" & j).Text).Range("M" & l + 1) = Application.WorksheetFunction.Sum(Sheets(.Range("S" & j).Text).Range("M2:M" & l))
    Sheets(.Range("S" & j).Text).Range("N" & l + 1) = Application.WorksheetFunction.Sum(Sheets(.Range("S" & j).Text).Range("N2:N" & l))
    Sheets(.Range("S" & j).Text).Range("O" & l + 1) = Application.WorksheetFunction.Sum(Sheets(.Range("S" & j).Text).Range("O2:O" & l))
End With
Next
Application.ScreenUpdating = True
End Sub

Thanks

 

[Shabbo]

Sir,

Getting error, please refer file.

 

[Nebu]

Replace the code with the following code

Sub SplitSheets()
Application.ScreenUpdating = False
Dim i, j, l As Long
On Error Resume Next
Sheet1.ShowAllData
i = Sheet1.Cells(Rows.Count, "S").End(xlUp).Row
For j = 2 To i
With Sheet1
    .Range("A1:O1").AutoFilter field:=4, Criteria1:=.Range("S" & j)
    Sheets.Add after:=Sheet1
    ActiveSheet.Name = .Range("S" & j)
    .Range("A1:O" & Sheet1.Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets(.Range("S" & j).Text).Range("A1")
    Application.CutCopyMode = False
    l = Sheets(.Range("S" & j).Text).Cells(Rows.Count, "A").End(xlUp).Row
    Sheets(.Range("S" & j).Text).Range("K" & l + 1) = Application.WorksheetFunction.Sum(Sheets(.Range("S" & j).Text).Range("K2:K" & l))
    Sheets(.Range("S" & j).Text).Range("L" & l + 1) = Application.WorksheetFunction.Sum(Sheets(.Range("S" & j).Text).Range("L2:L" & l))
    Sheets(.Range("S" & j).Text).Range("M" & l + 1) = Application.WorksheetFunction.Sum(Sheets(.Range("S" & j).Text).Range("M2:M" & l))
    Sheets(.Range("S" & j).Text).Range("N" & l + 1) = Application.WorksheetFunction.Sum(Sheets(.Range("S" & j).Text).Range("N2:N" & l))
    Sheets(.Range("S" & j).Text).Range("O" & l + 1) = Application.WorksheetFunction.Sum(Sheets(.Range("S" & j).Text).Range("O2:O" & l))
End With
Next
Application.ScreenUpdating = True
End Sub

It is the same code I have added an error handler to skip the error.

Thanks

 

[Shabbo]

Sir,
There is no movement in the workbook after pasting this code.

Can you please past in the sheet and let me know.

Thank you.

 

[Shabbo]

I am using version 2007.

 

[Nebu]

Find the attached.

Click on the Run button.

Thanks

 

[Shabbo]

Dear Sir,
When I posted more data and another data a lots of blank sheets are coming in the workbook.

 

[Nebu]

Hi:

The macro pick the vendor names from column S of your sheet . Update this column without any blanks there won't be any issues . Also remember to delete all the sheets and keep only main sheet before running the code, else the code with throw an error since the sheet names already exists in the workbook(you cannot have 2 worksheets with the same name in a workbook). If this is not solving your problem please upload your workbook here.

Thanks

 

[Shabbo]

Hi,
Problem remains same i have attached file for your ref.

After running macro its becoming so big and not able to upload.

I

Also its taking lots of time as well.

 

[Nebu]

Hi:

Its working alright find the attached. The mistake you did was you forgot to take out duplicate vendor names from the column S. In the column S you should have only unique vendor names, the code will basically pick one vendor at a time from Column S, filter for that vendor and copy the filtered row into a newly added worksheet and rename the sheet as the vendor name. It will also sum up the relevant columns where yo wanted sum. click on the run button to run the code it hardly take few seconds to run.

Thanks

 

[Shabbo]

Thanks a lot for your help.