Results 1 to 2 of 2

Thread: split data from one sheet to multiple workbooks

  1. #1

    split data from one sheet to multiple workbooks

    Register for a FREE account, and/
    or Log in to avoid these ads!


    I have a data which run in millions and wants to split based on the uniqueness of the data listed in column A. The Column A has unique names like Phase1, phase 2, phase 3 etc., based on this the data has to be copied of each phase to different file and rename it Phase 1 or Phase 2 of the unique identifier.

    There are two files one is which contains this data and another one which is the template where data needs to be populated and saved to specific path.

    Template path
    For Ex: I have the template in C://users/data/template.xlsx

    The files after copying and renaming has to save in C://users/data/rawdata

    Need help in doing this and appreciate your help in this regard.
    Attached Files Attached Files

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    Public Sub Splitdata()Dim this As Worksheet
    Dim ws As Worksheet
    Dim firstrow As Long
    Dim lastrow As Long
    Dim numrows As Long
        Application.ScreenUpdating = False
        Set this = ActiveSheet
        With this
            lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
                firstrow = .Columns("A").Find(.Cells(lastrow, "A").Value, after:=.Range("A1")).Row
                Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
                .Rows(firstrow).Resize(lastrow - firstrow + 1).Copy ws.Range("A2")
                .Rows(1).Copy ws.Range("A1")
                ws.Name = .Cells(lastrow, "A").Value
                .Rows(firstrow).Resize(lastrow - firstrow + 1).Delete
                lastrow = firstrow - 1
            Loop Until lastrow < 2
        End With
        Application.ScreenUpdating = True
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts