• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Splitting Source Data in several separate workbooks using Partner ID.

vaibhav more

New Member
Dear Team

I have a file of around 30000+ rows containing data of around 70 partners. Each partner has avg 400 rows with all details. Now I have to save data of each partner in to seperate excel file.
Hence currently I am copying the data and paste in to new excel and save it with name of that particular partner. since this is completely manual intervention, took lots of time and chances of error is high
Request you to pls suggest the idea to minimuze the time and zero error chances.

regards
vaibhav more
 
Hi,

I am using the below macro for splitting data and saving files individually

This macro creates a pivot of the data and uses it to split and save the files

Code:
Sub Split()
 
  ActiveSheet.Cells(1, 1).Select
  ActiveCell.CurrentRegion.Select
  srcdata = ActiveCell.CurrentRegion.Address
 
  ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
  srcdata).CreatePivotTable TableDestination:="", _
  TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
 
  ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
  ActiveSheet.Cells(3, 1).Select
 
  ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
  "PivotTable1").PivotFields("TOTAL.OD"), "Sum of TOTAL.OD", xlSum
 
  With ActiveSheet.PivotTables("PivotTable1").PivotFields("AREA.NAME")
  .Orientation = xlRowField
  .Position = 1
  End With
 
  Do While ActiveCell.Value <> ""
 
  Selection.ShowDetail = True
  ActiveSheet.Move
  ActiveSheet.Name = Range("A1").Value
  Range("A2").Select
  ActiveWindow.FreezePanes = True
  ActiveWorkbook.SaveAs Filename:="D:\New Folder\" & Range("A2").Text & ".xls", FileFormat:=xlNormal
 
  Loop
 
End Sub
 
Last edited:
Back
Top