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!

    Hi,

    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
    Code:
    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
            
            Do
            
                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
  •