Excel Worksheet

Other Topics
Tools
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

When you work in SAP Data migration, you'll be automatically forced to get more comfortable with Mircosoft Excel because of the extensive use of spreadsheets functionality to migrate data from non SAP systems to SAP. But SAP's mass updation tool - MASS has a limitation. It will process 6000 records maximum at a time for certain programs. Recently I had a situation where I have to upload 1.1 lakh entries into SAP, which means I have to split the entire data load into 20 jobs. It is not only cumbersome to split manually the 1.1 lakh entries into multiple sheets of 6000 each, it will also lead to manual errors & system performance issues.

 When I faced such issue recently, I approached our Big Daddy - Google and found this method easily working for me. It is about using the VBA macro to manipulate the spreadsheet into multiple worksheets of desired rows. It saved a lot of time for me and I am sure that it will come handy for you too at some point of time. Best part of this is that you don't need any third party tool for this splitting purpose.

The following VBA code can help you split the rows into multiple worksheets by rows count, do as follows:

1. Hold down the ALT + F11 key to open the Microsoft Visual Basic for Applications window.

excel03

2. Click Insert > Module, and paste the following code in the Module Window.

Sub SplitData()
'Updateby20140617
Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
SplitRow = Application.InputBox("Split Row Num", xTitleId, 5, Type:=1)
Set xWs = WorkRng.Parent
Set xRow = WorkRng.Rows(1)
Application.ScreenUpdating = False
For i = 1 To WorkRng.Rows.Count Step SplitRow
    resizeCount = SplitRow
    If (WorkRng.Rows.Count - xRow.Row + 1) < SplitRow Then resizeCount = WorkRng.Rows.Count - xRow.Row + 1
    xRow.Resize(resizeCount).Copy
    Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
    Application.ActiveSheet.Range("A1").PasteSpecial
    Set xRow = xRow.Offset(SplitRow)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

excel04

3. Then press F5 key to run the code, and a dialog pops out for selecting a range to split, and then click OK

excel05

4. In another dialog for you to specify the rows count.

excel06

5. Click OK, and the range are split into multiple sheets by the rows count.

excel07

Note: The split worksheets are placed at the back of the master worksheet.

Hope this post is helpful to you.